PowerQuery

Unify the same items

What was the issue?

Transform this table

to this table using PowerQuery.

How to do that

We have to load data to the PowerQuery.

Then group data.

With operation “All rows”.

We have to add new index column.

Using custom column.

Remove other columns

Than the “Custom” column.

Expand columns

You get indexes for each FruitId group.

Pivot the data

It is necessary to get the indexes into columns.

Merge columns

All columns must be dynamically merged because we do not know how many columns we will have.

Remove other columns

Other than the “Merged” column.

Split columns by delimiter

We have to split only the left-most delimiter.

Rename columns and load data back to excel

We have to split only the left-most delimiter.

Now we have the table what we wanted.

It works dynamically. It doesn’t care how many data we have.

M-Query code

let

    Source = Excel.CurrentWorkbook(){[Name=”Fruits”]}[Content],

    #”Changed Type” = Table.TransformColumnTypes(Source,{{“FruitId”, Int64.Type}, {“FruitName”, type text}}),

    #”Grouped Rows” = Table.Group(#”Changed Type”, {“FruitId”}, {{“Count”, each _, type table [FruitId=nullable number, FruitName=nullable text]}}),

    #”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Custom”, each Table.AddIndexColumn([Count], “Index”, 1)),

    #”Removed Other Columns” = Table.SelectColumns(#”Added Custom”,{“Custom”}),

    #”Expanded Custom” = Table.ExpandTableColumn(#”Removed Other Columns”, “Custom”, {“FruitId”, “FruitName”, “Index”}, {“FruitId”, “FruitName”, “Index”}),

    #”Pivoted Column” = Table.Pivot(Table.TransformColumnTypes(#”Expanded Custom”, {{“Index”, type text}}, “cs-CZ”), List.Distinct(Table.TransformColumnTypes(#”Expanded Custom”, {{“Index”, type text}}, “cs-CZ”)[Index]), “Index”, “FruitName”),

    #”Inserted Merged Column” = Table.AddColumn(#”Pivoted Column”, “Merged”, each Text.Combine(List.Transform(Record.FieldValues(_), Text.From), “,”), type text),

    #”Removed Other Columns1″ = Table.SelectColumns(#”Inserted Merged Column”,{“Merged”}),

    #”Split Column by Delimiter” = Table.SplitColumn(#”Removed Other Columns1″, “Merged”, Splitter.SplitTextByEachDelimiter({“,”}, QuoteStyle.Csv, false), {“Merged.1”, “Merged.2”}),

    #”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Merged.1”, Int64.Type}, {“Merged.2”, type text}}),

    #”Renamed Columns” = Table.RenameColumns(#”Changed Type1″,{{“Merged.1”, “FruitId”}, {“Merged.2”, “FruitName”}})    

in

    #”Renamed Columns”

I am enthusiastic user of Excel. During many years of practice, Excel has become my hobby. Power tools then took the possibility of working to the next level. I also like to use VBA or Powershell to automate not only Excel work.

Lukáš Karlovský

ORGANIZER