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.

Payment ID (C1)	Amount (C1)	Payment ID (C2)	Amount (C2)
6041012759	34.3	604962185	39.165
6041012764	146.3	604962185	239.9925
6041012764	146.3	604962186	44.76
6041012777	253.15	604962186	261.81
6041012777	253.15	604963066	94.5425
6041012778	253.15	604963066	241.7175
6041012808	252.9	604963067	94.5425
6041012809	258.74	604963067	196.3575
6041012876	152.89	604963068	39.165
6041012877	263.56	604963068	116.3825
6041012877	328.35	604963068	145.45
6041012878	258.74	604963070	42.51
6041012879	142.96	604963070	127.53
6041012880	252.93	604963070	141.7
6041012881	146.3	604963683	184.21
6041012881	146.3	604963684	14.17
6041012882	73.15	604963684	69.76

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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]