Forum Discussion
Unpivoting
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
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:
6 Replies
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:
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
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
Hi,
Download the Excel file from the link in my previous post. Open PBI desktop and go to File > Import > Excel.
