Hello,
Please help with Power Query function for the following-
I want last purchase date, 2nd last purchase date, 3rd last purchase date etc..from "PurchMnfQuery" and same for the quantity for each Item Code in "Inventory Query".
I have added a running count as a helper column based on the Posting Date. (*Posting Date Referred as Purchase Date)
As a reference, to my issue you can view the "WORKING" sheet in Inventory File which is done in excel.
Thanks
Hi Juliet,
Here is the InventoryQuery updated, in the last column you will have a table for each record, with the dates and quantities sorted:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
PMQ=Table.SelectColumns(PurchMnfQuery,{"Posting Date","Item Code","Quantity"}),
ChangedType = Table.TransformColumnTypes(PMQ,{{"Item Code", type text}}),
SortedRows = Table.Buffer(Table.Sort(ChangedType,{{"Posting Date", Order.Descending}})),
GroupedRows = Table.Group(SortedRows, {"Item Code"}, {{"Items", each _, type table [Posting Date=nullable date, Item Code=nullable text, Quantity=nullable number]}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"QTY", Int64.Type}, {"VALUE", Int64.Type}, {"Description", type text}, {"TYPE", type text}}),
#"Unit Price" = Table.AddColumn(#"Changed Type", "Unit Price", each [VALUE]/[QTY]),
#"Reordered Columns" = Table.ReorderColumns(#"Unit Price",{"TYPE", "Item Code", "Description", "QTY", "Unit Price", "VALUE"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Unit Price", Int64.Type}, {"Item Code", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type2", {"Item Code"}, GroupedRows, {"Item Code"}, "Last Orders", JoinKind.LeftOuter),
#"Expanded Last Orders" = Table.ExpandTableColumn(#"Merged Queries", "Last Orders", {"Items"}, {"Items"}),
#"Expanded Items" = Table.ExpandTableColumn(#"Expanded Last Orders", "Items", {"Posting Date", "Quantity"}, {"Items.Posting Date", "Items.Quantity"})
in
#"Expanded Items"
Hi Catalin
Thanks for responding
I would want you to see view the inventory file "working sheet" to see how i have done it. I don't want the column expanded within 1 column. I want all the date & qty matching running count 1 from PMQ table w.r.t each item code in InvQ table. Likewise for different running count column wise in invQ table.
Thanks in adv
Hi Juliet,
I see what you mean, but I dare to suggest a better layout and a proper table structure, having that structure from Working sheet, with the table increasing horizontally with more columns will generate more issues in the future.
Attached is a pivot table report that can be easily filtered with slicers to see the items you want, based on the query I provided already.
Ageing calculations and Age buckets can be easily replicated within power query, these fields can be also added as slicers to help you filter the report.
