Excel BI Power Query Challenge 54 Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ExtractDate = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}),
    CreateCrossProd = Table.AddColumn(ExtractDate, "Custom", each 
    [
        ProductList = List.Distinct( ExtractDate[Product] ),
        ActionList = List.Distinct( ExtractDate[Action]),
        MinDate = List.Min( ExtractDate[Date] ),
        MaxDate = List.Max( ExtractDate[Date] ),
        Products = {"Fruit", "Vegetable", "Meat"},
        Actions = {"Buy", "Sell" },
        Count = Number.From( MaxDate ) - Number.From (MinDate) + 1,
        DateList = List.Dates( MinDate, Count, #duration(1, 0, 0, 0))
    ]),
    RemoveOtherCols = Table.SelectColumns(CreateCrossProd,{"Custom"}),
    Expand = Table.ExpandRecordColumn(RemoveOtherCols, "Custom", {"ProductList", "ActionList", "DateList"}, {"ProductList", "ActionList", "DateList"}),
    ExpProductList = Table.ExpandListColumn(Expand, "ProductList"),
    ExpActionList = Table.ExpandListColumn(ExpProductList, "ActionList"),
    ExpDateList = Table.Distinct( Table.ExpandListColumn(ExpActionList, "DateList")),
    WorkTable = Table.RemoveColumns( Table.Join( ExpDateList, {"ProductList", "ActionList", "DateList"}, ExtractDate, {"Product", "Action", "Date"}, JoinKind.LeftOuter), {"Product", "Action", "Date"}),
    ReplaceNulls = Table.ReplaceValue(WorkTable,null,0,Replacer.ReplaceValue,{"Amt"}),
    Sort = Table.Sort(ReplaceNulls,{{"DateList", Order.Ascending}, {"ProductList", Order.Ascending}, {"ActionList", Order.Ascending}}),
    AddAmtSign = Table.Buffer( Table.AddColumn(Sort, "AmtSign", each if [Amt] = 0 then 0 else 
if [ActionList] = "Buy" then [Amt] else
[Amt] * -1)),
    AddIndex = Table.AddIndexColumn(AddAmtSign, "OverallOrder", 1, 1, Int64.Type),
    GroupbyDate = Table.Group(AddIndex, {"DateList"}, {{"All", each _, type table [ProductList=text, ActionList=text, DateList=date, Amt=number, AmtSign=number, OverallOrder=number]}}),
    AddGroupIndex = Table.RemoveColumns( Table.AddColumn(GroupbyDate, "GroupIndex", each Table.AddIndexColumn( [All], "GroupIndex", 1, 1)), "All"),
    Expand2 = Table.ExpandTableColumn(AddGroupIndex, "GroupIndex", {"ProductList", "ActionList", "Amt", "AmtSign", "OverallOrder", "GroupIndex"}, {"ProductList", "ActionList", "Amt", "AmtSign", "OverallOrder", "GroupIndex"}),
    Sort2 = Table.Sort(Expand2,{{"ProductList", Order.Ascending}, {"DateList", Order.Ascending}, {"ActionList", Order.Ascending}}),
    GroupBYProduct = Table.Group(Sort2, {"ProductList"}, {{"All", each _, type table [DateList=date, ProductList=text, ActionList=text, Amt=number, AmtSign=number, OverallOrder=number, GroupIndex=number]}}),
    AddGroupProdIndex = Table.RemoveColumns(  Table.AddColumn(GroupBYProduct, "AddIndexProd", each Table.AddIndexColumn( [All], "GroupProdIndex", 1, 1)), {"All", "ProductList"}),
    AddBalance = Table.AddColumn(AddGroupProdIndex, "Balance", each 
[
    a = List.Max( [AddIndexProd][GroupProdIndex]),
    a1 = [AddIndexProd][AmtSign],
    b = List.Generate(
 () => [x=1, y= List.Sum( List.FirstN( a1, x ))], 
           each [x] <= a, 
           each [ x = [x] + 1, y = List.Sum( List.FirstN( a1, x )) ],
           each [y] 

    )
][b]),
Table1 = Table.SelectColumns( AddBalance, "AddIndexProd"),
    ExpandTable1 = Table.ExpandTableColumn(Table1, "AddIndexProd", {"DateList", "ProductList", "ActionList", "Amt", "AmtSign", "OverallOrder", "GroupIndex", "GroupProdIndex"}, {"DateList", "ProductList", "ActionList", "Amt", "AmtSign", "OverallOrder", "GroupIndex", "GroupProdIndex"}),
    AddJoinIdx1 = Table.AddIndexColumn(ExpandTable1, "JoinIdx1", 1, 1, Int64.Type),
    Table2 = Table.SelectColumns( AddBalance, "Balance"),
    #"Expanded Balance" = Table.ExpandListColumn(Table2, "Balance"),
    AddJoinIdx2 = Table.AddIndexColumn(#"Expanded Balance", "JoinIdx2", 1, 1, Int64.Type),
    Combined = Table.Join(AddJoinIdx1, "JoinIdx1", AddJoinIdx2, "JoinIdx2"),
    RemovNClean = Table.SelectColumns(Combined,{"DateList", "ProductList", "ActionList", "Amt", "Balance"}),
    Rename = Table.RenameColumns(RemovNClean,{{"DateList", "Date"}, {"ProductList", "Product"}, {"ActionList", "Action"}}),
    FinalSort = Table.Sort(Rename,{{"Date", Order.Ascending}, {"Product", Order.Ascending}})
in
    FinalSort