Power Bi duplicate rows

0 votes

In Power BI, I have a table that looks like this:

ID
234
435
3435
58
48504
7820

I want to convert it to a table that looks like this:

ID
234-101
234-102
435-101
435-102
3435-101
343-102
58-101
58-102
48504-101
48504-102
7820-101
7820-102

Is this possible?

Oct 3, 2018 in Power BI by lina
• 8,220 points
13,610 views

2 answers to this question.

0 votes

Yes ofcourse this is possible. You can use the query editor for this purpose. just follow these steps:

step 1 -  Start with your data in the Query Editor 

step 2 - Add two additional columns for your suffixes. Click on the "Custom Column from Examples" button and then type in "234-101" in the first cell. After arrowing down to the next cell, it should auto-populate the rest. Do this again for "-102". 

step 3 - Unpivot the two new columns to get them into one. With the "ID" column selected, click on the dropdown for "Unpivot Columns" and click on "Unpivot Other Columns".

step 4 - Remove extra columns. In the resulting data, you will have the original "ID" column, along with two new ones; "Attribute" and "Value". Since the "Value" column contains the desired values, select the "ID" and "Attribute" columns, right click one of their headers, and select "Remove Columns". 

step 5 - Rename the "Value" column to "ID" and you're finished. 

Here is the resulting M code for all of those actions.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2UYrViVYyMTYF08YwhqkFRNzC1ACiwtzCyEApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({Text.From([ID], "en-US"), "-101"}), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "Merged.1", each Text.Combine({Text.From([ID], "en-US"), "-102"}), type text),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Inserted Merged Column1", {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"ID", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "ID"}})
in
    #"Renamed Columns"
answered Oct 3, 2018 by Kalgi
• 52,360 points
I think this can be done using DAX as well.
can you please put that method also as an answer so that i can choose between the two methods.
Awesome tip!!!!!! Thank you very much
0 votes

As mentioned by @Lina I'll post achieving the same using DAX. 

Just follow the mentioned steps, its as easy as it can get.

1. Start with your data in the data view. 

2. Click on "Enter Data" and add the data for the suffixes.

3. Click on "New Table" and enter the following formula.

NewData = CROSSJOIN(Data, Suffixes)

4. Click on "New Column and enter the following formula.

NewID = CONCATENATE(CONCATENATE(NewData[ID], "-"), NewData[Value])

answered Oct 3, 2018 by Nilesh
• 7,050 points

Related Questions In Power BI

0 votes
0 answers

How to group rows in Power BI query editor?

Can someone explain the steps how can ...READ MORE

Feb 4, 2020 in Power BI by ggm
• 140 points
1,052 views
0 votes
1 answer

Programmatically sign in - power bi

Basically: POST request to: https://login.microsoftonline.com/common/oauth2/token Body, form-url-encoded: grant_type: "password" scope: "openid" resource: "https://analysis.windows.net/powerbi/api" client_id: ...READ MORE

answered Sep 19, 2018 in Power BI by Kalgi
• 52,360 points
1,024 views
+1 vote
2 answers

Passing parameters to Power BI filter programmatically

In addition to @Kalgi's answer, there's another ...READ MORE

answered Sep 19, 2018 in Power BI by Nilesh
• 7,050 points
7,838 views
0 votes
1 answer

create interactive R plots in Power BI

 Right now Power BI only supports render ...READ MORE

answered Sep 20, 2018 in Power BI by Kalgi
• 52,360 points
1,056 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
990 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
2,447 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 18,570 points
1,127 views
+1 vote
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 52,360 points
1,417 views
+1 vote
1 answer

Power BI Pivot Colums to Rows

Hey @Nilesh, reshape the data in the ...READ MORE

answered Sep 19, 2018 in Power BI by Kalgi
• 52,360 points
553 views
0 votes
1 answer

Are 2 MM rows too much data for Power BI Online to manage?

PowerBI can easily handle 400MM rows and ...READ MORE

answered Sep 28, 2018 in Power BI by Kalgi
• 52,360 points
854 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP