Excel Forum

Transpose the columns into rows of one query to another query.

Alpha Juliet

New Member

Members

Forum Posts: 2

Member Since:
April 1, 2022

Offline

April 1, 2022 - 7:24 pm

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

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

April 2, 2022 - 4:10 pm

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"

Alpha Juliet

New Member

Members

Forum Posts: 2

Member Since:
April 1, 2022

Offline

April 2, 2022 - 11:18 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

April 3, 2022 - 3:20 pm

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.