Forum Discussion
Solved
Handling Poor Data in Power BI
Hi Everyone, I am very new to Power BI and attempting to teach myself how to use it. I have some very poorly layed out data that I wish to change the format of. Can anyone walk me through how to ...
LAH start a new blank query and copy this M code, follow these steps in your table, tweak the solution as you see fit.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJTy3KTFSK1YlWcs4vzSspqlQoyizOVkgsLk4tLs5NzSspVrCCSINJp9LizDygjEJyTmZuYkkqToWOeen5OdQ12AVicFE6UCwzjyizXYg120kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Group" = Table.AddColumn(#"Added Index", "Group", each [Index]-Number.Mod([Index],5), Int64.Type), #"Added Column Order" = Table.AddColumn(#"Added Group", "Column Order", each Number.Mod([Index],5), Int64.Type), #"Removed Index" = Table.RemoveColumns(#"Added Column Order",{"Index"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA")[#"Column Order"]), "Column Order", "Data"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"0", "Country"}, {"2", "Country risk assessments"}, {"4", "Business climate assessments"}}), #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Country", "Country risk assessments", "Business climate assessments"}) in #"Removed Other Columns"Output:
parry2k
Super User
Super UserLAH start a new blank query and copy this M code, follow these steps in your table, tweak the solution as you see fit.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJTy3KTFSK1YlWcs4vzSspqlQoyizOVkgsLk4tLs5NzSspVrCCSINJp9LizDygjEJyTmZuYkkqToWOeen5OdQ12AVicFE6UCwzjyizXYg120kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Group" = Table.AddColumn(#"Added Index", "Group", each [Index]-Number.Mod([Index],5), Int64.Type),
#"Added Column Order" = Table.AddColumn(#"Added Group", "Column Order", each Number.Mod([Index],5), Int64.Type),
#"Removed Index" = Table.RemoveColumns(#"Added Column Order",{"Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA")[#"Column Order"]), "Column Order", "Data"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"0", "Country"}, {"2", "Country risk assessments"}, {"4", "Business climate assessments"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Country", "Country risk assessments", "Business climate assessments"})
in
#"Removed Other Columns"
Output: