CODEWITHSUNDEEP
powerbipowerquery
tbur
tbur

Reputation: 2454

Expand Table Rows for Each Delimited Value in One Column's Cells

I'm an Excel VBA programmer who's recently discovered Power Query and Power BI.

Often, I will receive requests to change something like:

this.

Into a sort of un-pivoted version that looks like:

enter image description here

This can absolutely be done in VBA, but watching several Power Query videos (and now ordered three books!) It seems it would be much more efficient to do it in Power Query.

I get the over-all idea (create a table from the delimited list, for one) but don't know the language of the actual steps. And I really want to learn these new tools.

Can I buy a vowel? Phone a friend?

Upvotes: 2

Views: 2940

Answers (1)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

  1. Import the table into Power Query using From Table
  2. Click on the second column and choose "Split Column | By Delimiter" from the ribbon. Choose Comma as the delimiter and click OK.
  3. Right click on the first column and choose "Unpivot Other Columns".
  4. Click on the Attribute column and click on "Remove Columns" from the ribbon.

If you go to the Advanced Editor, you can see the code used to generate this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column2",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Column2.1", "Column2.2", "Column2.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

Upvotes: 3

Related Questions