Forum Discussion
Helper IIUnpivot Group of data
Helper IIIf 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?
Microsoft EmployeeHi 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
- MbProg
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
Hi MbProg,
Yes, my bad. Combine is also good to go, and faster.:smileyhappy:
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
- ImkeF
Community Champion
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 Attribute2At 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?)
Hello,
How would I remove duplicates if I have more than 2 attributes?
- ImkeF
Is it possible to unpivot the data on dax rather than power query ?
