Display differences between 2 columns as if they are the same column

[mhomol]

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?

[d_gosbell · Accepted 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.

[mhomol]

@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?

[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.