Forum Discussion
Solved
Expand column M
Good afternoon, Im requesting sucefully an API with pbi. This api give me 2 fields "id" and "fields". fields its an column in aech row a list, and the list contain 3 records and each record have ...
kevinEGE I have created sample data and added the steps on how you can do it. Start a new query -> click advanced editor and paste the M code. Follow the same steps in your data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.id = _t]), #"Changed column type" = Table.TransformColumnTypes(Source, {{"Column1.id", type text}}, "en-US"), #"Added List with Records" = Table.AddColumn(#"Changed column type", "Column1.fields", each {[Rate=10, Qty=20],[Product = "A", Customer = "B"], [Country = "Canada", Province = "BC"]}), #"Added Position To List" = Table.AddColumn(#"Added List with Records", "ListPos", each List.Transform({0..List.Count([Column1.fields])-1}, (x as number) => Record.FromList({[Column1.fields]{x}, x}, {"Record", "Pos"}))), #"Removed Original List Column" = Table.RemoveColumns(#"Added Position To List", {"Column1.fields"}), #"Expanded List" = Table.ExpandListColumn(#"Removed Original List Column", "ListPos"), #"Expanded List Record and Pos" = Table.ExpandRecordColumn(#"Expanded List", "ListPos", {"Record", "Pos"}, {"Record", "Pos"}), #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded List Record and Pos", {{"Pos", type text}}), List.Distinct(Table.TransformColumnTypes(#"Expanded List Record and Pos", {{"Pos", type text}})[Pos]), "Pos", "Record"), #"Expanded 1st Record" = Table.ExpandRecordColumn(#"Pivoted column", "0", {"Rate", "Qty"}, {"Rate", "Qty"}), #"Expanded 2nd Record" = Table.ExpandRecordColumn(#"Expanded 1st Record", "1", {"Product", "Customer"}, {"Product", "Customer"}), #"Expanded 3rd Record" = Table.ExpandRecordColumn(#"Expanded 2nd Record", "2", {"Country", "Province"}, {"Country", "Province"}) in #"Expanded 3rd Record"
