Forum Discussion
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 dozens of these queries); I can make the API request to get the data from that specific query. I'm trying to get a single table as the end result that I can then work with in Excel. There are lots of these individual queries (with different numbers of columns and data types), and new ones constantly being created or modified, so I'm trying to come up with the template to just read the JSON file and intelligently build the resulting table (e.g., without hard-coding column names).
The general format of the JSON reply is:
- 2 lines of key/value pairs I don't care about
- A list of the column headers ("headers")
- A list of lists containing each record in that table ("data")
Sample JSON file is:
{
"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"
]
]
}
I've been able to get this far -
let
Source = Json.Document(Web.Contents("https://www.customerapiurl.com", [Headers=[#"Api-Key"="APIKeyData"]])),
#"Converted to Table" = Record.ToTable(Source),
#"Removed Top Rows" = Table.Skip(#"Converted to Table",2)
in
#"Removed Top Rows"
Drilling into the "headers" list (which will change for each different api request), I get:
Drilling into the "data" list, I get the list of lists:
Any help would be greatly appreciated; after a couple of days of googling/experimenting/struggling with this, I've hit the proverbial wall.
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.
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
5 Replies
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 ToTablePlease post the sample JSON in a usable format, not as a screenshot.
Done! Edited original post.
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.
