Brian Julius solution to Crispo Mwangi PQ Challenge - June 16 2024

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    AddIndex2 = Table.AddIndexColumn(AddIndex, "Index.1", 0, 1, Int64.Type),
    IntDiv = Table.TransformColumns(AddIndex2, {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    Modulo = Table.TransformColumns(IntDiv, {{"Index.1", each Number.Mod(_, 2), type number}}),
    Piv = Table.Pivot(Table.TransformColumnTypes(Modulo, {{"Index.1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Modulo, {{"Index.1", type text}}, "en-US")[Index.1]), "Index.1", "Date & Orders"),
    RemCol = Table.RemoveColumns(Piv,{"Index"}),
    RenCol = Table.RenameColumns(RemCol,{{"0", "Date"}, {"1", "Order"}}),
    ReType = Table.TransformColumnTypes(RenCol,{{"Date", type date}}),
    DupeDate = Table.DuplicateColumn(ReType, "Date", "Month"),
    ExtractMon = Table.TransformColumns(DupeDate,{{"Month", Date.Month, Int64.Type}}),
    Group = Table.Group(ExtractMon, {"Month"}, {{"All", each _, type table [Date=nullable date, Order=number, Month=number]}, {"MaMonth", each List.Max([Date]), type nullable date}}),
    Expand = Table.ExpandTableColumn(Group, "All", {"Date", "Order"}, {"All.Date", "All.Order"}),
    DateSubtract = Table.AddColumn(Expand, "Subtraction", each Duration.Days([MaMonth] - [All.Date]), Int64.Type),
    Filter = Table.SelectRows(DateSubtract, each ([Subtraction] = 0)),
    RemCol2 = Table.RemoveColumns(Filter,{"All.Date", "MaMonth", "Subtraction"}),
    RenCol2 = Table.RenameColumns(RemCol2,{{"All.Order", "Closing Order"}})
in
    RenCol2