Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello there!
I need to normalize a table in Power BI but im not able to this do this on my own.
Can someone please help me step by step how to reach my goal.
Here is a link with sample data. (Table Structure at the moment and how my goal would look like) https://easyupload.io/t116d1
Solved! Go to Solution.
OK, got the data.
Here's the Advanced Editor code: (for these types of problems, I do one Unpivot -> Split the attribute column to get column names -> Pivot)
let
Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\UntitledUnp.xls"), null, true),
Sheet2 = Source{[Name="Sheet1"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"ID", Int64.Type}, {"Article_1", type text}, {"Article_2", type text}, {"Article_3", type text}, {"Article_4", type text}, {"Amount_1", Int64.Type}, {"Amount_2", Int64.Type}, {"Amount_3", Int64.Type}, {"Amount_4", Int64.Type}, {"Price_1", Currency.Type}, {"Price_2", Currency.Type}, {"Price_3", Currency.Type}, {"Price_4", Currency.Type}, {"Minutes_1", Int64.Type}, {"Minutes_2", Int64.Type}, {"Minutes_3", Int64.Type}, {"Minutes_4", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," 0","",Replacer.ReplaceText,{"Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date", "ID"}, "Attribute", "Value"),
#"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "NULL")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Descending}, {"Article", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute.2"})
in
#"Removed Columns"
The column names and types might be different at your side.
Good luck
Could you post your data on a different site please and i'll have a look. Edge browser doesn't like easyupload.
Sorry, still not happy. I think it's the file. Can you virus check the file please?
OK, got the data.
Here's the Advanced Editor code: (for these types of problems, I do one Unpivot -> Split the attribute column to get column names -> Pivot)
let
Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\UntitledUnp.xls"), null, true),
Sheet2 = Source{[Name="Sheet1"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"ID", Int64.Type}, {"Article_1", type text}, {"Article_2", type text}, {"Article_3", type text}, {"Article_4", type text}, {"Amount_1", Int64.Type}, {"Amount_2", Int64.Type}, {"Amount_3", Int64.Type}, {"Amount_4", Int64.Type}, {"Price_1", Currency.Type}, {"Price_2", Currency.Type}, {"Price_3", Currency.Type}, {"Price_4", Currency.Type}, {"Minutes_1", Int64.Type}, {"Minutes_2", Int64.Type}, {"Minutes_3", Int64.Type}, {"Minutes_4", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," 0","",Replacer.ReplaceText,{"Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date", "ID"}, "Attribute", "Value"),
#"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "NULL")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Descending}, {"Article", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute.2"})
in
#"Removed Columns"
The column names and types might be different at your side.
Good luck
@Jensej , You need multiple unpivot
https://kohera.be/blog/power-bi/how-to-unpivot-twice/
or New Table in DAX like
New Table =
Union(
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_1], Table[Price_1], Table[MINUTES_1]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_2], Table[Price_2], Table[MINUTES_2]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_3], Table[Price_3], Table[MINUTES_3]),
summarize(Table, Table[Date], Table[ID], Table[AMOUNT_4], Table[Price_4], Table[MINUTES_4])
)
Tried this multiple pivot but it doesn't work.
I selected all articles,amounts,price,minutes and then unpivot. First i got a error message and had to change type using locale on all columns and after that it worked.
After that i have two columns Attribute with (article_1,amount_1,price_1 and so on) Value with (7601,7601-A,3,1,45 and so on.
Then i split Attribute column by position right 2 to get rid of all the _1, _2 and so on. I also deleted that column after.
Then i had a Attribute column with article,price,amount,minutes and a column with values. I marked both and then choose pivot on value table but the end results is wrong.. .
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |