Excel BI Power Query Challenge 157 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddGlobalOrigOrder = Table.AddIndexColumn(Source, "GlobalOrigOrder", 0, 1, Int64.Type),
    UnpivotOther = Table.UnpivotOtherColumns(AddGlobalOrigOrder, {"Group", "GlobalOrigOrder"}, "Attribute", "Value"),
    SortNBuffer = Table.Buffer( Table.Sort(UnpivotOther,{{"Group", Order.Ascending}, {"Attribute", Order.Ascending}, {"GlobalOrigOrder", Order.Ascending}})),
    Group = Table.Group(SortNBuffer, {"Group", "Attribute"}, {{"All", each _, type table [Group=number, GlobalOrigOrder=number, Attribute=text, Value=any]}}),
    AddGlobalIdx1 = Table.AddColumn(Group, "GlobalIdx1", each Table.AddIndexColumn([All], "Global1", 1, 1)),
    AddGlobalIdx0 = Table.SelectColumns( Table.AddColumn(AddGlobalIdx1, "GlobalIdx0", each Table.AddIndexColumn([GlobalIdx1], "Global0", 0, 1)), "GlobalIdx0"),
    Expand = Table.ExpandTableColumn(AddGlobalIdx0, "GlobalIdx0", {"Group", "GlobalOrigOrder", "Attribute", "Value", "Global1", "Global0"}, {"Group", "GlobalOrigOrder", "Attribute", "Value", "Global1", "Global0"}),
    Headers = Table.ColumnNames( Expand ),
    HeadersB = List.Transform( Headers, each _ & "_B"),
    TableB = Table.FromColumns( Table.ToColumns( Expand ), HeadersB ),
    Join = Table.Join( Expand, {"Group", "Attribute", "Global0"}, TableB, {"Group_B", "Attribute_B", "Global1_B"}, JoinKind.LeftOuter ),
    RemoveCol = Table.SelectColumns(Join,{"Group", "GlobalOrigOrder", "Attribute", "Value", "Value_B"}),
    Sort = Table.Sort(RemoveCol,{{"Group", Order.Ascending}, {"Attribute", Order.Ascending}, {"GlobalOrigOrder", Order.Ascending}}),
    AddPrevValue = Table.RemoveColumns( Table.AddColumn(Sort, "PrevValue", each if [Value_B] = null then [Value] else [Value_B]), "Value_B"),
    AddChange = Table.AddColumn(AddPrevValue, "Change", each if Text.From( [Value] ) = Text.From([PrevValue]) then " " else [Value]),
    RemCol2 = Table.RemoveColumns(AddChange,{"Value", "PrevValue"}),
    Pivot = Table.Pivot(RemCol2, List.Distinct(RemCol2[Attribute]), "Attribute", "Change"),
    Sort2 = Table.RemoveColumns( Table.ReorderColumns( Pivot, Table.ColumnNames( AddGlobalOrigOrder )), "GlobalOrigOrder")
in
    Sort2

Link to original Excel BI post :
https://www.linkedin.com/feed/update/urn:li:activity:7164466865094828032?updateEntityUrn=urn%3Ali%3Afs_feedUpdate%3A%28V2%2Curn%3Ali%3Aactivity%3A7164466865094828032%29