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 what header IsEqual = what Region.

3. I may have to inlude more columns in any of the part of this excel. Hence, the solution may be a flexible to unpivot more columns.

 

Power BI PBX sample Files ->https://1drv.ms/u/s!AkBVVNsuRUvzbBndSCMWArFt8Zo

 

Sample file in Excel --> https://1drv.ms/x/s!AkBVVNsuRUvzcY-nNxEoK_Jouvw

 

 

 

 

 

 

 

 

 

 

 

I am neither a developer nor a PBI expert. Help will be appreciated

 

P.S. : Just back to using PBI after a longtime :-)

Thanks

Chandan.

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

     

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    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