Forum Discussion

mhomol's avatar
mhomol
Icon for Helper I rankHelper I
5 years ago
Solved

Display 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 tha...
Need Help
  • d_gosbell's avatar
    d_gosbell
    5 years ago

    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's avatar
d_gosbell
Icon for Super User rankSuper User
5 years ago

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's avatar
mhomol
Icon for Helper I rankHelper I
5 years ago

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's avatar
    d_gosbell
    Icon for Super User rankSuper User
    5 years ago

    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.