Forum Discussion
zephyr325
2 years agoRegular Visitor
Repeatable Table Creation from JSON file - List of headers, and list of lists for data rows.
I'm working with a customer's application where I can request/get a JSON file back from an API request. The source of these files are queries the customer has built in their application (there are do...
- 2 years ago
let Source = Json.Document(" { ""count"": 22, ""keys"": 22, ""headers"": [ ""ID"", ""First Name"", ""Last Name"", ""Age"", ""Campus"", ""Member Since"" ], ""data"": [ [ ""42311"", ""John"", ""Smith"", ""43"", ""Northside"", ""Mar 17, 2019"" ], [ ""56288"", ""Tom"", ""Peterson"", ""65"", ""Southside"", ""Jul 7, 2006"" ], [ ""15344"", ""Mary"", ""Story"", ""23"", ""Eastside"", ""Sep 1, 2021"" ] ] } "), data = Source[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), Source[headers]), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Age", Int64.Type}, {"Member Since", type date}}) in #"Changed Type"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
- 2 years ago
Hi zephyr325, different approach:
Result
let Source = Json.Document("{ ""count"": 22, ""keys"": 22, ""headers"": [ ""ID"", ""First Name"", ""Last Name"", ""Age"", ""Campus"", ""Member Since"" ], ""data"": [ [ ""42311"", ""John"", ""Smith"", ""43"", ""Northside"", ""Mar 17, 2019"" ], [ ""56288"", ""Tom"", ""Peterson"", ""65"", ""Southside"", ""Jul 7, 2006"" ], [ ""15344"", ""Mary"", ""Story"", ""23"", ""Eastside"", ""Sep 1, 2021"" ] ] }"), ToTable = Table.FromRows(Source[data], Source[headers]) in ToTable