Forum Discussion
Table.ExpandListColumn - list splitting and avoiding rows duplicating
Hi all, I am parsing a JSON with nested records and lists. Now I'm using the Advanced Editor and I'm trying a way to split my list and take just the field of the first occurrency (it's dating sorted...
try adapting this code, it takes first record from the list of records:
let Source = Json.Document(File.Contents("C:\tests\test.json")), #"Converted to Table" = Record.ToTable(Source), #"Transposed Table" = Table.Transpose(#"Converted to Table"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"data", type any}, {"message", type any}, {"status", type any}}), #"Expanded data" = Table.ExpandRecordColumn(#"Changed Type", "data", {"ratio", "annualized", "payments"}, {"ratio", "annualized", "payments"}), #"Expanded payments" = Table.ExpandRecordColumn(#"Expanded data", "payments", {"rows"}, {"rows"}), transform = Table.TransformColumns(#"Expanded payments", {{"rows", each _{0}, type text}}) in transform
try adapting this code, it takes first record from the list of records:
let
Source = Json.Document(File.Contents("C:\tests\test.json")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"data", type any}, {"message", type any}, {"status", type any}}),
#"Expanded data" = Table.ExpandRecordColumn(#"Changed Type", "data", {"ratio", "annualized", "payments"}, {"ratio", "annualized", "payments"}),
#"Expanded payments" = Table.ExpandRecordColumn(#"Expanded data", "payments", {"rows"}, {"rows"}),
transform = Table.TransformColumns(#"Expanded payments", {{"rows", each _{0}, type text}})
in
transform
it worked without the "type text" at the end
thanks a lot