Transform Data with Power Query

[johnmpl]

Hi to all!

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

*	A	B	C
Barcelona	Valencia
Valencia	Barcelona
Madrid	Roma
Roma	Madrid
Barcelona	Madrid
Madrid	Barcelona
Barcelona	Valencia
Valencia	Barcelona
Madrid	Roma
Roma	Madrid
Barcelona	Roma
Roma	Barcelona

<tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Origin[/TD]
[TD="align: center"]Destiny[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]AA[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]AA[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]AA[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]AA[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]AA[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]AA[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]AB[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]AB[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]AC[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]AC[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]AC[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]AC[/TD]

</tbody>

Into this:

*	E	F	G	H	I

<tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Cities[/TD]
[TD="bgcolor: #c0c0c0, align: center"]No. Of Destinies[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Destiny 1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Destiny 2[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Destiny 3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]Barcelona[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Valencia[/TD]
[TD="align: center"]Madrid[/TD]
[TD="align: center"]Roma[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Valencia[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Barcelona[/TD]

[TD="align: center"][/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]Madrid[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Roma[/TD]
[TD="align: center"]Barcelona[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]Roma[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Madrid[/TD]
[TD="align: center"]Barcelona[/TD]

</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"