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
lbendlin
Super User
2 years agoPlease post the sample JSON in a usable format, not as a screenshot.
zephyr325
2 years agoRegular Visitor
Done! Edited original post.
- lbendlin2 years ago
Super User
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.