Forum Discussion
Add detail row as difference between details sum vs header value
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.
Hi Anonymous
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"
1 Reply
- PiEye
Resolver II
Hi Anonymous
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"