Unpivoting

[Anonymous]

Hi,

I have data something like this and I would like to unpivot data as indicated below. Appreciate if anyone can help on this as when I tried to unpivot(unpivot only selected columns) the resulting output is not what I was expecting.

Data:

Cust Name          State         Measure 1 Name             Measure 1 Value              Measure 2 Name             Measure 2 Value    etc

========      =====      ===========            ============           ============           ===========  ===

Adventure           TX           Cust Service                              85.34                        Quality                                93.78

Meta Yoga           FL           Cust Service                              78.00                        Quality                                87.45

Expecting(unpivoted):

Cust Name               State        Measure Name       Measure Value

=======              ====       ==========       ==========

Adventure                TX           Cust Service              85.34

Adventure                TX           Quality                      93.78

etc...

Meta Yoga                FL           Cust Service              78.00

Meta Yoga                FL           Quality                      87.45

etc...

Thanks in advance,

SN

[Ashish_Mathur]

Hi,

You may refer to my solution in this workbook.

Hope this helps.

[shishir999]

Hi Ashish,

I am having same scenario, but still not able to resovle it.

Could you please share the solution in a PBIX format along with sample data file.

Thanks

Shishir

[Ashish_Mathur]

Hi,

Download the Excel file from the link in my previous post.  Open PBI desktop and go to File > Import > Excel.

[shishir999]

I will try it, but, Is it possible for you to send the file, as due to restrictions at work, unable to access the Drive location.

Thanks

Shishir

[drewlewis15 · Accepted solution]

I have found a way to get to your result, but surely there is a better option... Usually, unpivoting works well and is very simple, but like you mentioned, I am having issues getting your desired result through an unpivot alone.  Maybe someone else can provide a cleaner solution, but until then:

• Select all Name columns (Measure 1 Name, Measure 2 Name, etc.) and select Unpivot Columns
• Then select all Value columns (Measure 1 Value, Measure 2 Value, etc.) and select Unpivot Columns
• You will end up with a result that looks like this:

• Now, create a custom column that checks to see if Attribute = Attribute.1... For this example, I removed "Name" from Attribute and "Value" from Attribute.1, and then created a custom column with the following statement:
  • if [Attribute] = [Attribute.1] then "Keep" else "Delete"
• I then just filtered to only show rows where that custom column = "Keep" and then deleted the Attribute and Attribute.1 columns.
• Final result:

[Anonymous]

This is great and works for me(even though it is not an elegant way to do, I guess that is what "shaping" is all about). However, I was struggling a bit with custom column. I was using the DAX sort of syntax but after some time I realized need to use 'M' language. I used the below for ny custom column :

if Text.Start([Attribute],6) = [Attribute.1] then "Keep" else "Delete"

Thanks so much

-SN