Complex Unpivot help needed

[Anonymous]

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.

[v-lili6-msft · Accepted solution]

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

Here is DEMO , please try it.

https://www.dropbox.com/s/29beqahcl6tgct3/Complex%20Unpivot%20help%20needed.pbix?dl=0

Best Regards,

Lin

[Anonymous]

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

[v-lili6-msft]

Hi,@Chandan

 This is the excel fiile :

https://www.dropbox.com/s/drekyxfz1fb63sh/New%20Microsoft%20Excel%20Worksheet.xlsx?dl=0

Best Regards,

Lin

[Anonymous]

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