cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
francomaestri
Frequent Visitor

Normalization

Hi!

I'm having problems normalizing some data. I want to create a new normalized table inside Power BI using the original one. The goal is that it is almos automatic, so someone can just add new data in the file in the original format.

 

The original is like this (with many categorys an):

DateCategory ACategory B Category C
March 2020$34$22$50
April 2020$46$26$57
May 2020$52$30$61
............

 

And I need it like this:

DatePriceCategory 
March 2020$34A
March 2020$22B
March 2020$50C
April 2020$46A
April 2020$26B
April 2020$57C
May 2020$52A
May 2020$30B
May 2020$61C
.........

 

Any ideas? I was trying to use some calculated columns, but I couldn't do it.

2 ACCEPTED SOLUTIONS
ValtteriN
Super User
Super User

Hi,

You can unpivot the data in poowerquery.

First select the columns:

ValtteriN_0-1639635506154.png


Now right-click a column header and select "unpivot columns"

End result:

ValtteriN_1-1639635552560.png


I hope this helps to solve your issue and if it does consider accepting this as a solution and giving the post a thumbs up!





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

Proud to be a Super User!




View solution in original post

Eyelyn9
Community Support
Community Support

Hi @francomaestri , 

 

Please follow @ValtteriN  's suggest to use unpivot,below is the whole M syntax that you can paste in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sSs5QMDIwMlDSUVIxNgGRRkYg0tRAKVYnWsmxoCgzB67AxAysAEyamoMV+CZWwqVNwTqNwWwzQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Category A" = _t, #"Category B " = _t, #"Category C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category A", Currency.Type}, {"Category B ", Currency.Type}, {"Category C", Currency.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Extracted Text Range" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Middle(_, 9, 1), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Range",{{"Attribute", "Category"}, {"Value", "Price"}})
in
    #"Renamed Columns"

Eyelyn9_0-1639984364945.png

 

If you want to do it using DAX, since It is not allowed to use DAX to change the table structure, you could use UNION() to create a new table :

NewTable = 
UNION(
  SELECTCOLUMNS('Table', "Date",[Date], "Category", "A", "Price", [Category A]),
  SELECTCOLUMNS('Table', "Date",[Date], "Category", "B", "Price", [Category B]),
  SELECTCOLUMNS('Table', "Date",[Date], "Category", "C", "Price", [Category C]))

Eyelyn9_1-1639984990787.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Eyelyn9
Community Support
Community Support

Hi @francomaestri , 

 

Please follow @ValtteriN  's suggest to use unpivot,below is the whole M syntax that you can paste in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sSs5QMDIwMlDSUVIxNgGRRkYg0tRAKVYnWsmxoCgzB67AxAysAEyamoMV+CZWwqVNwTqNwWwzQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Category A" = _t, #"Category B " = _t, #"Category C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category A", Currency.Type}, {"Category B ", Currency.Type}, {"Category C", Currency.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Extracted Text Range" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Middle(_, 9, 1), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Range",{{"Attribute", "Category"}, {"Value", "Price"}})
in
    #"Renamed Columns"

Eyelyn9_0-1639984364945.png

 

If you want to do it using DAX, since It is not allowed to use DAX to change the table structure, you could use UNION() to create a new table :

NewTable = 
UNION(
  SELECTCOLUMNS('Table', "Date",[Date], "Category", "A", "Price", [Category A]),
  SELECTCOLUMNS('Table', "Date",[Date], "Category", "B", "Price", [Category B]),
  SELECTCOLUMNS('Table', "Date",[Date], "Category", "C", "Price", [Category C]))

Eyelyn9_1-1639984990787.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ValtteriN
Super User
Super User

Hi,

You can unpivot the data in poowerquery.

First select the columns:

ValtteriN_0-1639635506154.png


Now right-click a column header and select "unpivot columns"

End result:

ValtteriN_1-1639635552560.png


I hope this helps to solve your issue and if it does consider accepting this as a solution and giving the post a thumbs up!





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

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors