CODEWITHSUNDEEP
powerbidax
SUMguy
SUMguy

Reputation: 1673

Power BI - Duplicate Rows

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 even possible within Power BI?

Upvotes: 0

Views: 5838

Answers (2)

Joe G
Joe G

Reputation: 1776

I thought of two ways to do this, though there are probably others.

NOTE - I prefer the second method as it lets the "101" and "102" be data driven allowing them to be changed or added to in the future more easily.

A) Through the Query Editor (requires hard-coding the "101"/"102" values)

Step 1: Start with your data in the Query Editor Step A1 - Start

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 A2 - Custom Columns

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 A3 - Unpivot 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 A4 - Remove Columns

Step 5: Rename the "Value" column to "ID" and you're finished. Step A5 - Rename Column

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"

B) Through DAX

Step 1: Start with your data in the data view. Step B1 - Start

Step 2: Click on "Enter Data" and add the data for the suffixes. (Skip this if these numbers are sourced somewhere else) Step B2 - Add Suffix Data

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

NewData = CROSSJOIN(Data, Suffixes)

Step B3 - New Table

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

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

Step B4 - New Column

If you want the new column to be named "ID", you'll need to rename the old "ID" column first, as you can't simply remove it like was done in the first method.

Upvotes: 2

Marc Pincince
Marc Pincince

Reputation: 5202

If you're okay with using Power BI's query editor (Power Query) for this, you can do it with this query code:

let
Source = Table1,
#"Inserted Merged Column3" = Table.AddColumn(Source, "DelimitedListWithSuffixes", each Text.Combine({[ID], "-101,", [ID],"-102"}), type text),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Inserted Merged Column3", {{"DelimitedListWithSuffixes", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "DelimitedListWithSuffixes"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DelimitedListWithSuffixes", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"DelimitedListWithSuffixes"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"DelimitedListWithSuffixes", "ID"}})
in
#"Renamed Columns"

(Table1 is your original ID column table.)

Upvotes: 2

Related Questions