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...
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"

     

     

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.