Forum Discussion

zephyr325's avatar
zephyr325
Regular Visitor
2 years ago
Solved

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...
  • lbendlin's avatar
    lbendlin
    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.

  • dufoq3's avatar
    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