Add detail row as difference between details sum vs header value

[TomikBlack]

Hi Experts, 

any ideas how to Add detail row as difference between details sum vs header value? I have many files xlsx files where header sum and also detail is presented, but sum <> sum(details) and for difference i d like to generate new product it can be always "c" where ll be difference from header counted for particular file. 

Thanks Tomas

sample file 1

header table
sum
120
detail table
product	value
a	50
b	20

----------------------------------------------

sample file 2

header table
sum
100
detail table
product	value
a	70
b	20

-------------------------------------------

expected output pbi, in yellow line i d like to have "c". Thinking of Table.InsertRows, but its not dynamic, so iam out of ideas.

[PiEye · Accepted solution]

Hi @TomikBlack 

I'm assuming you have two tables in power query that look like this:

I was able to create rows for "c" by converting the detail values to negative, appending the header, and grouping and summing to get a difference, and appending the details back onto it.

Here are the steps:

New query created by referencing details & adding a new column which is the negative of the values:

Header rows added on. We can then group, and "sum" to get the difference. 

These grouped values are our "c"

We can then append the details back on

I can then create a matrix in the UI with this additional line:

HTH -Full M Query for this table below

Pi

Please: Don't forget to mark as an answer if this helped!

let
Source = Detail,
#"Removed Columns" = Table.RemoveColumns(Source,{"product"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "sum", each -[value]),
#"Appended Query" = Table.Combine({#"Added Custom", Header}),
#"Grouped Rows" = Table.Group(#"Appended Query", {"Source.Name"}, {{"value", each List.Sum([sum]), type number}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "product", each "c"),
#"Appended Query1" = Table.Combine({#"Added Custom1", Detail}),
#"Reordered Columns" = Table.ReorderColumns(#"Appended Query1",{"Source.Name", "product", "value"})
in
#"Reordered Columns"