cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MbProg Regular Visitor
Regular Visitor

Unpivot Group of data

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

Unbenannt.png

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

8 REPLIES 8
jmalone Regular Visitor
Regular Visitor

Re: Unpivot Group of data

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 Regular Visitor
Regular Visitor

Re: Unpivot Group of data

@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 New Contributor
New Contributor

Re: Unpivot Group of data

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,

9.PNG

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.

10.PNG11.PNG

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.

14.PNG

 

4. Create a new table with the formula below:

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

See the result table:

12.PNG

 

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

 

 

 

 

MbProg Regular Visitor
Regular Visitor

Re: Unpivot Group of data

When unpivoting the data and creating so much duplicates and a new filter table, how much does that affect the performance when dealing with tables with about 1 Mio records?

Now I have also found another way by using the combine option in Power Query. Is that not faster?

v-micsh-msft New Contributor
New Contributor

Re: Unpivot Group of data

Hi MbProg,

Yes, my bad. Combine is also good to go, and faster.Smiley Happy

Point should be noticed is that when using combine, there are also dumplicate rows created. Removing them should be similar to the steps mentioned here. Or if you have any other better solution, please share it if you don't mind.

Regards

 

 

 

 

Super User
Super User

Re: Unpivot Group of data

Possibly, this performs best:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssvKslQ0lEyBGEDIGFqAGaBSbCAoZGBUqxOtJJrYnEJkGsEwiBxM1QlQNIYojA8FazQGIRB4uaoSoCkCURhcH4p2G4TEAZJWKCqAZJA18TGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Region = _t, #"2012" = _t, #"2013" = _t, #"2014" = _t, #"Diff 2012" = _t, #"Diff 2013" = _t, #"Diff 2014" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Region"}, "Attribute", "Value"),
    ClassifyValues = Table.AddColumn(#"Unpivoted Other Columns", "Diff", each if Text.Start([Attribute], 4) = "Diff" then "Diff " else "Value" ),
    StandardizeYear = Table.ReplaceValue(ClassifyValues,"Diff ","",Replacer.ReplaceText,{"Attribute"}),
    PivotBack = Table.Pivot(StandardizeYear, List.Distinct(StandardizeYear[Diff]), "Diff", "Value"),
    Attribute2 = Table.AddColumn(PivotBack, "Attribute2", each "Diff "&[Attribute])
in
    Attribute2

 

At least, it is fully dynamic - so if new years are added or removed, no need to adjust the query.

(? Is the last step actually needed?)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




nreynolds3 Visitor
Visitor

Re: Unpivot Group of data

Hello,

 

How would I remove duplicates if I have more than 2 attributes?

Highlighted
Super User
Super User

Re: Unpivot Group of data

Hi @nreynolds3,

I don't understand your question. Could you please give more details?

Thx.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries