Expand column M

[kevinEGE]

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 2 rows, like this:

i want to have my final model like:

Columns> ID /  Quantidade / unidade / data e hora.

Anyone can help with M code? i really appreciate!! thank u so much!

[parry2k]

@kevinEGE glad to hear it was helpful. Cheers!

[kevinEGE]

This was a perfect answer!!!

I've tried this rn and solved perfectly my problem.

thank you so much @parry2k !!

[parry2k · Accepted solution]

@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"