Forum Discussion

mddistributor1's avatar
mddistributor1
Regular Visitor
4 years ago

Power Query - Data manipulation

I have data setup like the below table. Every time something is received the system creates a dash that is one higher than the prior one which shows everything that is yet to be received. I'm trying ...
Need Help
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
4 years ago

= #table({"VendorID","PoNum","Month End","Backorder Amount"},List.TransformMany(Table.Group(Source,{"VendorID","PoNum"},{"n",each Table.ToRows(Table.Sort(_,"Received Date"))},1,(x,y)=>let fx=(x)=>Text.Format("#[VendorID]-#[PoNum]",x&[PoNum=Text.BeforeDelimiter(Text.From(x[PoNum]),"-")]) in Value.Compare(fx(x),fx(y)))[n],each List.Accumulate(List.Skip(_),{{},_{0}{3}},(x,y)=>{x{0}&{List.FirstN(y,2)&{Date.EndOfMonth(Date.From(y{5})),x{1}-y{4}}},x{1}-y{4}}){0},(x,y)=>y))

 

  • mddistributor1's avatar
    mddistributor1
    Regular Visitor
    4 years ago

    This looks great, but I might need a little more explaination on this to completely understand what's going on.