Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Complex Unpivot help needed

Hi Experts,   1. I need help to Un-Pivot the a lengthy pivot table in PBI. 2. See color coding in the input & Output. But Yellow colored is the Region name, see the link to excel / PBX file for wh...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    8 years ago

    Hi@Chandan

    According to your description ,follow my steps and give it a try as below: Step 1: Duplicate three table based on you input table like this:

    Step 2:
    In the first copy table, remove AmericasValueVariableUSD ,APACValueVariableUSD ,EuropeValueVariableUSD ,IndiaValueVariableUSD ,MEAAValueVariableUSD ,NonRgnlValueVariableUSD ,AmericasValueValueFixedUSD ,
    APACValueValueFixedUSD ,EuropeValueValueFixedUSD ,IndiaValueValueFixedUSD ,MECAAValueValueFixedUSD , NonRgnlValueFixedUSD column.
    then select  AmericasVertical ,APACVertical ,EuropeVertical ,IndiaVertical ,MECAAVertical ,NonRegionalVertical columns and "Unpivot only selected columns" .
    filter value "N.A."
     
    Step 3: Rename columns "Attribute"->"Headers_For_UnPivot",   "Value"->"Regional Verticals" and Replace values "Headers_For_UnPivot" by "Desired_Region"
     
    Step 4:
    In the second copy table, remove AmericasVertical ,APACVertical ,EuropeVertical ,IndiaVertical ,MECAAVertical ,NonRegionalVertical ,AmericasValueValueFixedUSD ,
    APACValueValueFixedUSD ,EuropeValueValueFixedUSD ,IndiaValueValueFixedUSD ,MECAAValueValueFixedUSD , NonRgnlValueFixedUSD column.
    then select  AmericasValueVariableUSD ,APACValueVariableUSD ,EuropeValueVariableUSD ,IndiaValueVariableUSD ,MEAAValueVariableUSD ,NonRgnlValueVariableUSD columns and "Unpivot only selected columns" .
    filter value "-"
     
    Step 5: Rename columns "Attribute"->"Headers_For_UnPivot",   "Value"->"RegionalValueVariableUSD" and Replace values "Headers_For_UnPivot" by "Desired_Region"
     
    Step 6:
    In the second copy table, remove AmericasVertical ,APACVertical ,EuropeVertical ,IndiaVertical ,MECAAVertical ,NonRegionalVertical ,
    AmericasValueVariableUSD ,APACValueVariableUSD ,EuropeValueVariableUSD ,IndiaValueVariableUSD ,MEAAValueVariableUSD ,NonRgnlValueVariableUSD  column.
    then select  AmericasValueValueFixedUSD ,APACValueValueFixedUSD ,EuropeValueValueFixedUSD ,IndiaValueValueFixedUSD ,MECAAValueValueFixedUSD , NonRgnlValueFixedUSD columns and "Unpivot only selected columns" .
    filter value "-"
     
    Step 7: 
    Rename columns "Attribute"->"Headers_For_UnPivot",   "Value"->"RegionalValueValueFixedUSD" and Replace values "Headers_For_UnPivot" by "Desired_Region"
     
    Step 8:
    Use merge function (Home ->Combine ->Merge Queries) twice as below :
    1.
    Expend data
    2.
    Expend data

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Lin v-lili6-msft ,

 

While I am trying on my database your suggestion, is it possible to post the excel fiile that you have used ?

I tried to us my excel through edit queries however, there is this two error of Key / Table.

 

Thanks,

Chandan

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thank You v-lili6-msft Lin for the solution !