Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi peeps,
Can anyone please help in solving a little tranformation challenge that I am facing as shown below by using Power BI/Query?
Thanks,
Solved! Go to Solution.
Try Using the following M-Query for this purpose
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLgrGQwywguBmKlgFnGEFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, roi = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"roi", type text}}), Partition = Table.Group(#"Changed Type", {"id"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"roi", "Index"}, {"roi", "Index"}), #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each if [Index] = 1 then "roi1" else if [Index] = 2 then "roi2" else "roi3") in #"Added Custom"
The Output is as shown below
you can try the following as well.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each "roi"), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"id", type text}, {"roi", type text}, {"Custom", type text}}), Group_index = Table.Group(#"Changed Type", {"id"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded index" = Table.ExpandTableColumn(Group_index, "index", {"roi", "Custom", "Index"}, {"roi", "Custom", "Index.1"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded index",{{"roi", type text}, {"Custom", type text}, {"Index.1", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Custom]&[Index.1]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index.1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "roi") in #"Pivoted Column"
Br, T
Hi @vw_golf_mk3,
Please refer to Iamnvt's solution which can be achieved easily by GUI.
Group data based on id column.
Then, make a little modification to the highlighted code. And expand the "all data" field.
Choose "Index" column then pivot table.
Best regards,
Yuliana Gu
Hi @vw_golf_mk3,
Please refer to Iamnvt's solution which can be achieved easily by GUI.
Group data based on id column.
Then, make a little modification to the highlighted code. And expand the "all data" field.
Choose "Index" column then pivot table.
Best regards,
Yuliana Gu
Try Using the following M-Query for this purpose
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWBsJLgrGQwywguBmKlgFnGEFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, roi = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"roi", type text}}), Partition = Table.Group(#"Changed Type", {"id"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"roi", "Index"}, {"roi", "Index"}), #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each if [Index] = 1 then "roi1" else if [Index] = 2 then "roi2" else "roi3") in #"Added Custom"
The Output is as shown below
you can try the following as well.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each "roi"), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"id", type text}, {"roi", type text}, {"Custom", type text}}), Group_index = Table.Group(#"Changed Type", {"id"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded index" = Table.ExpandTableColumn(Group_index, "index", {"roi", "Custom", "Index"}, {"roi", "Custom", "Index.1"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded index",{{"roi", type text}, {"Custom", type text}, {"Index.1", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Custom]&[Index.1]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index.1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "roi") in #"Pivoted Column"
Br, T