Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have the below scenario:
I want to transform this data to achieve the following:
Can anyone help on how to achieve this using power bi?
Thanks
Solved! Go to Solution.
@alir22456 Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUQooyk8pTS4BsmN1cEgZ4ZYyRpUywm2gEW4DjXHrMiZOlzFuKROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Product = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Products", each _, type table [Customer=nullable text, Product=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.SelectColumns([Products], "Product"))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Products"})
in
#"Removed Columns"
@alir22456 Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUQooyk8pTS4BsmN1cEgZ4ZYyRpUywm2gEW4DjXHrMiZOlzFuKROl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Product = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Products", each _, type table [Customer=nullable text, Product=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.SelectColumns([Products], "Product"))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Products"})
in
#"Removed Columns"