Normalization

[francomaestri]

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

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

And I need it like this:

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

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

[ValtteriN · Accepted solution]

Hi,

You can unpivot the data in poowerquery.

First select the columns:

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

End result:

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!

[v-eqin-msft · Accepted solution]

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"

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

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 · Accepted solution]

Hi,

You can unpivot the data in poowerquery.

First select the columns:

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

End result:

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!