Forum Discussion
Helper IDisplay differences between 2 columns as if they are the same column
Imagine I have a table with the following information for a single row:
| Key | G17 | G18 | G26 | G33 | G34 |
| 2020 | 34,000 | 35,000 | 17,000 | 18,000 | 25,000 |
Here's how we would like to display that same row in a report, as if some of the different columns are actually just 2 halves of the same label (or "column"):
| Book | Tax | Difference | |
| + Contribution - Cash | G17 34,000 | G33 18,000 | 16,000 |
| + Contribution - Property | G18 35,000 | G34 25,000 | 10,000 |
| + Management Fees | G26 17,000 | 17,000 |
I'm fine with the Differences and those measures. I'm assuming this has to start with an UNPIVOT, but I'm struggling with the details. Can anyone assist here?
Yes I would start with an unpivot so that you have your data in the following format
Key Column Amount 2020 G17 34,000 2020 G18 35,000 2020 G26 17,000 2020 G33 18,000 2020 G34 25,000 Then you would need to have a lookup table to translate each or your "G" values into the rows and columns from your output. I'm not sure what you call these items, but in the example below I've called the rows "Account" and the book/tax values the "type"
Column Account Type G17 Contribution - Cash Book G18 Contribution - Property Book G26 Management Fees Book G33 Contribution - Cash Tax G34 Contribution - Property Tax Then you can do a merge join between your original unpivoted data and the lookup table. That should give you and output like the following
Key Column Amount Account Type 2020 G17 34,000 Contribution - Cash Book 2020 G18 35,000 Contribution - Property Book 2020 G26 17,000 Management Fees Book 2020 G33 18,000 Contribution - Cash Tax 2020 G34 25,000 Contribution - Property Tax From there you can either pivot the type column in Power Query or you could leave it as it is above and use measures to calculate the Book and Tax values.
3 Replies
- d_gosbell
Super User
Yes I would start with an unpivot so that you have your data in the following format
Key Column Amount 2020 G17 34,000 2020 G18 35,000 2020 G26 17,000 2020 G33 18,000 2020 G34 25,000 Then you would need to have a lookup table to translate each or your "G" values into the rows and columns from your output. I'm not sure what you call these items, but in the example below I've called the rows "Account" and the book/tax values the "type"
Column Account Type G17 Contribution - Cash Book G18 Contribution - Property Book G26 Management Fees Book G33 Contribution - Cash Tax G34 Contribution - Property Tax Then you can do a merge join between your original unpivoted data and the lookup table. That should give you and output like the following
Key Column Amount Account Type 2020 G17 34,000 Contribution - Cash Book 2020 G18 35,000 Contribution - Property Book 2020 G26 17,000 Management Fees Book 2020 G33 18,000 Contribution - Cash Tax 2020 G34 25,000 Contribution - Property Tax From there you can either pivot the type column in Power Query or you could leave it as it is above and use measures to calculate the Book and Tax values.
mhomold_gosbell this all makes perfect sense and was where my head was going as well. I have a huge issue though, which is that UNPIVOT is currently not supported with Dataverse as the dataset (even if it's Imported - I still have an open ticket with MS but I'm not hopeful).
Do you think I could do this with a transform of another kind to still get to the end result that you describe?
- d_gosbell
If you are using Table.Unpivot in Power Query it should not matter what data source you are using. If it only fails against Dataverse then it sounds like a bug in the Dataverse connector and I would be hopeful that Microsoft would either respond to your ticket with a fix or a workaround.
If this is a query folding bug then *maybe* adding a call to Table.Buffer before doing the unpivot would force the unpivot to be done in the mashup engine, but I'm just speculating.