Forum Discussion

MbProg's avatar
MbProg
Icon for Helper II rankHelper II
10 years ago

Unpivot Group of data

Hello I have imorted this data in Power BI and want to unpivot it but like this:

How can I do it in Power BI Desktop? I tried a lot, but could not find a way till now

Need Help

9 Replies

  • jmalone's avatar
    jmalone
    Icon for Resolver III rankResolver III
    10 years ago

    I was able to achieve your result in the Query Editor, but there may be a better/more straightforward solution. Here's what I did:

     

    1. Duplicate your original query/table

    2. Remove the "Difxxxx" columns from the original query

    3. Remove the "2012 etc" columns from the duplicate query

    4. Unpivot columns in each query. You should now have two tables that are unpivoted how you want. Next step is to merge them.

    5. In the Duplicate query, add a custom column with formula = Text.End([Attribute], 4). This will turn values "Dif 2012" into "2012"

    6. In the Duplicate query, add a custom column that concatenates "Region" with your new custom column. The result will be something like "North2012". This will be your ID column for merging the queries.

    7. In the Original query, add a custom column that concatenates "Region" with the "Attribute" column. The result will be similar "North2012" values to step 6.

    8. In the Original query, Merge the Duplicate query using these new "ID" fields as the matching columns.

    9. Remove the ID column. Your table should now appear as desired.

     

     

    Like I said, there may be a better solution, but this should work for you.

    • MbProg's avatar
      MbProg
      Icon for Helper II rankHelper II
      10 years ago

      jmalone

      If you duplicate the original table, does that mean duplicate data traffic when Power BI is refreshing its data on a scheduled basic? Or does it import the data once and duplicates it inside the application?

      • v-micsh-msft's avatar
        v-micsh-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        Hi MbProg,

        We could Unpivot columns under Power BI query Editor.  Please follow the steps below:

        1.Open Query Editor with the data source, Then select the column with the same type that you would like to have their values in one column (Press Ctrl and then use mouse left click to select two or more columns), then click Unpivot Column,

        2. Choose the other same type columns that you would like to put into one single column, and select Unpivot Columns. Change the column name as you required, then under home tab, click close and apply.

        3. We need to remove the duplicate values, create a column under Power BI data View with the formula below:

        Column = if(value(Sheet1[Attribute])=value(right(Sheet1[Attribute.1],4)),1,0)

        This method requires your Diff 2012 last string to be the same as the Year column.

         

        4. Create a new table with the formula below:

        Table = filter(Sheet1, Sheet1[Column]=1)

        See the result table:

         

        For the duplicate action under Power BI Query Editor, this is happened within Power BI Desktop application, once data is imported, it will not affect the data source structure, and no more data traffics when modeling the data with the data source.

        If any further questions , please feel free to post back.

        Regards