Transform Data with Power Query

[johnmpl]

Hi to all!

This time i want to transform data, using Power Query, from this:

*	A	B	C
3	Company	Origin	Destiny
4	AA	Barcelona	Valencia
5	AA	Valencia	Barcelona
6	AA	Madrid	Roma
7	AA	Roma	Madrid
8	AA	Barcelona	Madrid
9	AA	Madrid	Barcelona
10	AB	Barcelona	Valencia
11	AB	Valencia	Barcelona
12	AC	Madrid	Roma
13	AC	Roma	Madrid
14	AC	Barcelona	Roma
15	AC	Roma	Barcelona

<tbody>
</tbody>

Into this:

*	E	F	G	H	I
3	Cities	No. Of Destinies	Destiny 1	Destiny 2	Destiny 3
4	Barcelona	3	Valencia	Madrid	Roma
5	Valencia	1	Barcelona
6	Madrid	2	Roma	Barcelona
7	Roma	2	Madrid	Barcelona

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


I already know how to do it with formulas and vba. But I wanna know how can I get this result with Power Query. Thx in advanced for your answers. Blessings!

 

[billszysz]

Hi johnmpl,
Try this code below

let
    Source =  Table.FromRows({{"AA","Barcelona","Valencia"},{"AA","Valencia","Barcelona"},{"AA","Madrid","Roma"},{"AA","Roma","Madrid"},{"AA","Barcelona","Madrid"},{"AA","Madrid","Barcelona"},{"AB","Barcelona","Valencia"},{"AB","Valencia","Barcelona"},{"AC","Madrid","Roma"},{"AC","Roma","Madrid"},{"AC","Barcelona","Roma"},{"AC","Roma","Barcelona"}},  {"Company", "Origin", "Destiny"}),
    #"Changed Type" =  Table.TransformColumnTypes(Source,{{"Company", type text}, {"Origin",  type text}, {"Destiny", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Company"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
     #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Origin"}, {{"How  many", each Table.RowCount(_), type number}, {"records", each  Record.FromList(_[Destiny], List.Transform({1..Table.RowCount(_)}, each  "Destiny " & Text.From(_))), type record}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"How many", Order.Descending}}),
    LstOfFieldNames = Record.FieldNames( #"Sorted Rows"{0}[records]),
    #"Expand Record Column" = Table.ExpandRecordColumn(#"Sorted Rows", "records", LstOfFieldNames, LstOfFieldNames)
in
    #"Expand Record Column"

Regards

 

[johnmpl]

Nice Billszysz! Excellent Solution. I have troubles with this line:

#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"How many", Order.Descending}}),
But I erase that, and worked like a charm. Thx and Blessings!

 

[Useful]

Hello,

Your question is more popular now. I've made video trick how to pass this.
If you've interested with another solution with an example then visit here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Origin"}, {{"GrBy", each Table.Transpose(Table.FromList([Destiny]))}}),
    MainTable = Table.AddColumn(#"Grouped Rows", "No. Of Destinies", each Table.ColumnCount([GrBy])),
    ListDistinct = List.Distinct(List.Combine(Table.AddColumn(MainTable, "ColNames", each Table.ColumnNames([GrBy]))[ColNames])),
    Finish = Table.ExpandTableColumn(MainTable,"GrBy",ListDistinct),
    #"Converted to Table" = Table.FromList(ListDistinct, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    DestinColNam = Table.AddColumn(#"Converted to Table", "Custom", each Text.Replace([Column1],"Column","Destiny")),
    ColNamList = Table.ToRows(DestinColNam),
    Custom2 = Table.RenameColumns(Finish,ColNamList),
    #"Reordered Columns" = Table.ReorderColumns(Custom2,List.InsertRange(DestinColNam[Custom],0,{"Origin", "No. Of Destinies"}))
in
    #"Reordered Columns"