Summary from Pivot Tables

[neeraj2050]

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

 

[neeraj2050]

Hi,

Would this be possible

Regards
Neeraj

 

[AlanSidman]

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[td="bgcolor:#ECF0F0, align:center"]G[/td]
[td="bgcolor:#ECF0F0, align:center"]H[/td]
[td="bgcolor:#ECF0F0, align:center"]I[/td]
[td="bgcolor:#ECF0F0, align:center"]J[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Location[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Raw Material/Others[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]>151[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]121-150[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]91-120[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]61-90[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]31-60[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]15-30[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8-14[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]0-7[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Logistics[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Logistics[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Raw Material[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Raw Material[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Raw Material[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]V[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Raw Material[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Z[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Others[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[/tr]
[/table]

Is this what you are looking for. If so, I will share results, if not, please advise what should be different and be specific.

 

[neeraj2050]

@AlanSidman : Yes, this is I am looking for

 

[AlanSidman]

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.

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