Power Bi duplicate rows

Question

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?

Answer 1

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"

Comments

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

Answer 2

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])