• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summary from Pivot Tables

Hi All,

In my data, summary is prepared from pivot table. But since my data get refreshed and there might be some changes happen in Location details and Raw material/others name then my summary should consider those changes along with figures

I have attached the file and shared raw data, visualized pivot table, summary1, summary2, but my expectation is summary3 which I am unable to visualize but shared the requirement into attachment

Any help would be appreciated

Regards
Neeraj
 

Attachments

  • Excel Query.xlsx
    12.5 KB · Views: 13
vABCDEFGHIJ
1LocationRaw Material/Others>151121-15091-12061-9031-6015-308-140-7
2ALogistics1
3BLogistics2
4CRaw Material37
5ARaw Material4
6BRaw Material5
7VRaw Material6
8ZOthers8

Is this what you are looking for. If so, I will share results, if not, please advise what should be different and be specific.
 
Using power query, I loaded both your tables to the PQ Editor. I then merged the two tables 2 to 1 with a left outer join, removed unneeded columns and then grouped the rows. Attached is the file. Here is the M code for the merged table which is the result of these actions. Power Query is a function within Excel that is available in versions 2010 and later. See the link in my signature block if you are not familiar.
Code:
let
    Source = Table.NestedJoin(Table1, {"Vendor Code"}, Table2, {"Vendor Code"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {">151 DAYS", "121-150 DAYS", "91-120 DAYS", "61-90 DAYS", "31-60 DAYS", "15-30 DAYS", "8-14 DAYS", "0-7 DAYS", "Total"}, {">151 DAYS", "121-150 DAYS", "91-120 DAYS", "61-90 DAYS", "31-60 DAYS", "15-30 DAYS", "8-14 DAYS", "0-7 DAYS", "Total"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Ageing", "Grand Total", "Total"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Location", "Raw Material/Others"}, {{">151", each List.Sum([#">151 DAYS"]), type number}, {"121-150", each List.Sum([#"121-150 DAYS"]), type number}, {"91-120", each List.Sum([#"91-120 DAYS"]), type number}, {"61-90", each List.Sum([#"61-90 DAYS"]), type number}, {"31-60", each List.Sum([#"31-60 DAYS"]), type number}, {"15-30", each List.Sum([#"15-30 DAYS"]), type number}, {"8-14", each List.Sum([#"8-14 DAYS"]), type number}, {"0-7", each List.Sum([#"0-7 DAYS"]), type number}})
in
    #"Grouped Rows"

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
 

Attachments

  • Excel Query.xlsx
    26 KB · Views: 2
Back
Top