Repeatable Table Creation from JSON file - List of headers, and list of lists for data rows.

[zephyr325]

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"

...which will give me this result:

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.

[lbendlin · Accepted solution]

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.

[zephyr325]

@dufoq3  and @lbendlin - thank you!  Both solutions work, and both also get me around the problem I've been having getting web API results into the Mac version of Excel which has been a LOOOONG struggle for me.  Much appreciated!

[dufoq3 · Accepted solution]

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]

Please post the sample JSON in a usable format, not as a screenshot.

[zephyr325]

Done!  Edited original post.

[lbendlin · Accepted solution]

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.