Excel BI Power Query Challenge 147 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToRecord = Table.CombineColumnsToRecord(Source, "Value", Table.ColumnNames(Source)),
    ToTable = Table.TransformColumns(ToRecord, {"Value", each Record.ToTable(_)}),
    AddOriginalRow = Table.AddIndexColumn(ToTable, "OriginalRow", 1, 1, Int64.Type),
    Expand = Table.ExpandTableColumn(AddOriginalRow, "Value", {"Name", "Value"}, {"Name", "Values"}),
    AddIsPopulated = Table.AddColumn(Expand, "IsPopulated", each if [Values] = null then 0 else 1),
    AddIndex = Table.AddIndexColumn(AddIsPopulated, "Index", 0, 1, Int64.Type),
    AddRunningTotal = Table.AddColumn(AddIndex, "RunningTotal", each List.Sum( List.FirstN( AddIndex[IsPopulated], [Index] + 1 ))),
    AddRTPrevRow = Table.AddColumn(AddIndex, "RTPrevRow", each List.Sum( List.FirstN( AddIndex[IsPopulated], [Index] ))),
    ReplaceNull = Table.ReplaceValue(AddRTPrevRow,null,0,Replacer.ReplaceValue,{"RTPrevRow"}),
    AddGrouping = Table.AddColumn(ReplaceNull, "Grouping", each if [Name] = "Cust ID"  then Number.IntegerDivide( [RTPrevRow], List.Count( Table.ColumnNames( Source ))) else null),
    FillDown = Table.FillDown(AddGrouping,{"Grouping"}),
    SelCols = Table.SelectColumns(FillDown,{"Name", "Values", "OriginalRow", "Grouping"}),
    GroupByGroupingName = Table.Group(SelCols, {"Grouping", "Name"}, {{"MaxValue", each List.Max([Values]), type any}, {"All", each _, type table [Name=text, Values=any, OriginalRow=number, Grouping=number]}}),
    Expand2 = Table.ExpandTableColumn(GroupByGroupingName, "All", {"OriginalRow"}, {"OriginalRow"}),
    RemovGrouping = Table.RemoveColumns(Expand2,{"Grouping"}),
    #"Pivoted Column" = Table.Pivot(RemovGrouping, List.Distinct(RemovGrouping[Name]), "Name", "MaxValue"),
    RemovOrigRow = Table.RenameColumns( Table.RemoveColumns(#"Pivoted Column",{"OriginalRow"}), {"Type", "Typ"}),
    GroupByCustID = Table.Group(RemovOrigRow, {"Cust ID"}, {{"All", each _, type table [Cust ID=number, Cust Name=text, Amount=number, Typ=text]}}),
    AddRowcounter = Table.RemoveColumns( Table.AddColumn(GroupByCustID, "RowCounter", each Table.AddIndexColumn( [All], "Rowcounter", 1, 1)), "All"),
    Expand3 = Table.ExpandTableColumn(AddRowcounter, "RowCounter", {"Cust Name", "Amount", "Typ", "Rowcounter"}, {"Cust Name", "Amount", "Typ", "Rowcounter.1"}),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Expand3, {{"Rowcounter.1", type text}}, "en-US"),{"Typ", "Rowcounter.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Type")
in
    Merge