Pivot tables - Consildating payments into one

[24parmar2]

Hi everyone,

I'm new to this forum. I need some help with pivot tables:

I'm trying to consolidate payments Ids together, specifically payments which have the same ID but different payments need to be combines and totalled into one. I done a head-start with pivot tables.

I comparing and combining payments from two different tables. If i do a pivot table from each table (C1 and C2) separately it works but i need to match the ID together and calculate the difference as some payment id will be in both tables, this i don;t know how to do quickly or efficiently. This is small example so ID's in C1 don't appear in C2.

The alternative I tried, was using the data range of both tables but the payments ID from C2 would be appeared to be in different format. It has the - icon by it allowing you to expand or collapse that ID, why is it doing for some payments and not all. I am so confused in getting this to work.

Could some one please help me out.

[TABLE="width: 548"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Payment ID (C1)[/TD]
[TD]Amount (C1)[/TD]
[TD]Payment ID (C2)[/TD]
[TD]Amount (C2)[/TD]
[/TR]
[TR]
[TD="align: right"]6041012759[/TD]
[TD="align: right"]34.3[/TD]
[TD="align: right"]604962185[/TD]
[TD="align: right"]39.165[/TD]
[/TR]
[TR]
[TD="align: right"]6041012764[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604962185[/TD]
[TD="align: right"]239.9925[/TD]
[/TR]
[TR]
[TD="align: right"]6041012764[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604962186[/TD]
[TD="align: right"]44.76[/TD]
[/TR]
[TR]
[TD="align: right"]6041012777[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604962186[/TD]
[TD="align: right"]261.81[/TD]
[/TR]
[TR]
[TD="align: right"]6041012777[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604963066[/TD]
[TD="align: right"]94.5425[/TD]
[/TR]
[TR]
[TD="align: right"]6041012778[/TD]
[TD="align: right"]253.15[/TD]
[TD="align: right"]604963066[/TD]
[TD="align: right"]241.7175[/TD]
[/TR]
[TR]
[TD="align: right"]6041012808[/TD]
[TD="align: right"]252.9[/TD]
[TD="align: right"]604963067[/TD]
[TD="align: right"]94.5425[/TD]
[/TR]
[TR]
[TD="align: right"]6041012809[/TD]
[TD="align: right"]258.74[/TD]
[TD="align: right"]604963067[/TD]
[TD="align: right"]196.3575[/TD]
[/TR]
[TR]
[TD="align: right"]6041012876[/TD]
[TD="align: right"]152.89[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]39.165[/TD]
[/TR]
[TR]
[TD="align: right"]6041012877[/TD]
[TD="align: right"]263.56[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]116.3825[/TD]
[/TR]
[TR]
[TD="align: right"]6041012877[/TD]
[TD="align: right"]328.35[/TD]
[TD="align: right"]604963068[/TD]
[TD="align: right"]145.45[/TD]
[/TR]
[TR]
[TD="align: right"]6041012878[/TD]
[TD="align: right"]258.74[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]42.51[/TD]
[/TR]
[TR]
[TD="align: right"]6041012879[/TD]
[TD="align: right"]142.96[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]127.53[/TD]
[/TR]
[TR]
[TD="align: right"]6041012880[/TD]
[TD="align: right"]252.93[/TD]
[TD="align: right"]604963070[/TD]
[TD="align: right"]141.7[/TD]
[/TR]
[TR]
[TD="align: right"]6041012881[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604963683[/TD]
[TD="align: right"]184.21[/TD]
[/TR]
[TR]
[TD="align: right"]6041012881[/TD]
[TD="align: right"]146.3[/TD]
[TD="align: right"]604963684[/TD]
[TD="align: right"]14.17[/TD]
[/TR]
[TR]
[TD="align: right"]6041012882[/TD]
[TD="align: right"]73.15[/TD]
[TD="align: right"]604963684[/TD]
[TD="align: right"]69.76[/TD]
[/TR]
</tbody>[/TABLE]


Thank You

 

[sandy666]

downolad and open file I gave you

Data - Show Queries
double click on any table there it will open PowerQuery window
then on the left side unfold Queries click on any and you'll see on the right side steps
you can edit each steps if you see little gear on the end of the step or you can open Advanced Editor from the ribbon to see M-code

for each table is a different M-code that's why I posted link to the file

 

[sandy666]

for the first table (C1)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="tblC1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payment ID (C1)", Int64.Type}, {"Amount (C1)", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Payment ID (C1)"}, {{"Value (C1)", each List.Sum([#"Amount (C1)"]), type number}})
in
    #"Grouped Rows"[/SIZE]

for the second table (C2)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="tblC2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payment ID (C2)", Int64.Type}, {"Amount (C2)", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Payment ID (C2)"}, {{"Value (C2)", each List.Sum([#"Amount (C2)"]), type number}})
in
    #"Grouped Rows"[/SIZE]

for the result table

[SIZE=1]let
    Source = Table.NestedJoin(tblC1,{"Payment ID (C1)"},tblC2,{"Payment ID (C2)"},"tblC2",JoinKind.Inner),
    #"Expanded tblC2" = Table.ExpandTableColumn(Source, "tblC2", {"Payment ID (C2)", "Value (C2)"}, {"Payment ID (C2)", "Value (C2)"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded tblC2",null,0,Replacer.ReplaceValue,{"Payment ID (C1)", "Value (C1)", "Payment ID (C2)", "Value (C2)"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [#"Value (C1)"] - [#"Value (C2)"], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Value (C1)", "Value (C2)"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Payment ID (C1)", type text}, {"Payment ID (C2)", type text}})
in
    #"Changed Type"[/SIZE]