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,110 points
1,322 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
• 40,420 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.
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
• 6,880 points

Related Questions In Power BI

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
• 40,420 points
81 views
0 votes
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
• 6,880 points
2,402 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
• 40,420 points
66 views
0 votes
1 answer

Refresh power bi report every hour

You’ll have a Power BI service option ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,070 points
253 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,060 points
77 views
0 votes
1 answer

Unable to install connector for Power Bi and PostgreSQL

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

answered Aug 21, 2018 in Power BI by nirvana
• 3,060 points
596 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
• 14,070 points
114 views
0 votes
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
• 40,420 points
109 views
0 votes
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
• 40,420 points
32 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
• 40,420 points
37 views