Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 result...
Need Help
  • drewlewis15's avatar
    drewlewis15
    8 years ago

    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:

drewlewis15's avatar
drewlewis15
Solution Specialist
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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