Forum Discussion

KrisCart's avatar
KrisCart
New Member
3 years ago
Solved

JSON headers at top of file not with data

Hi, I am new to bringing data in via api. I have a json report file brought into power query via api. After clearing out the rows that are just titles etc I am left with this:

 My issue is the headers are one list and the data is a seperate one. Other files I have used have had the headers repeat in the data but this one does not. I want to get to a flat table but I can't work out how to seperate the headers and data. 

 

When I click the two arrows I get to this stage:

This has the headers at the top and the rows still as a list below. 

 

Can anyone offer a solution please? 

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

     

     

    let
  Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\response.txt")),
  #"Converted to Table" = Table.FromList(
    Source[rows],
    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[columnNames]
  )
in
  #"Split Column by Delimiter"

     

     

4 Replies

  • KrisCart's avatar
    KrisCart
    New Member
    3 years ago

    Hi lbendlin,

     

    Thanks for the pointers. File attached, Sample JSON 

     

    I have stripped it down to 2 records for ease and have changed any business data. 

     

    If you could point me towards any suitable function that would be great. It seems such a simple thing that I know I am missing something. 

     

    Thanks

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

       

       

      let
  Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\response.txt")),
  #"Converted to Table" = Table.FromList(
    Source[rows],
    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[columnNames]
  )
in
  #"Split Column by Delimiter"

       

       

      • KrisCart's avatar
        KrisCart
        New Member
        3 years ago

        Thanks, the original source was an api but I addressed that by swapping the code line with the filename for the four lines of the api call.

         

        It all seems to be working now - thank you very much.