cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MbProg
Helper II
Helper II

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

9 REPLIES 9
jmalone
Resolver III
Resolver III

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.

@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?

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

 

 

 

 

Hello,

 

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

Hi @nreynolds3,

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

Thx.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Is it possible to unpivot the data on dax rather than power query ?

 

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?

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

 

 

 

 

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?)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors