Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

v-eqin-msft
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
v-eqin-msft
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors