Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
@d_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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |