Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Parsing out JSON query

Hello,

 

I'm having a difficult time parsing out 2 rows of lists from a web api. The dates exist in the header lists & the values under the data. How can I merge them together and expand in 1 table? What I have so far:

 

 

let
url = "https://api.socialbakers.com/1/aggregated-metrics",
body = "{
""profiles"": [{""id"": ""36144455"",""platform"":""twitter""}],
""date_start"": ""2020-07-01"",
""date_end"": ""2020-09-30"",
""metric"": ""insights_impressions"",
""dimensions"": [{""type"": ""date.day""}]
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[Headers=[#"Content-Type"="application/json; charset=utf-8",
Authorization="Basic ""######"""],
Content = Text.ToBinary(body)]))
in
Source

 

Thank You!

 

 

 

 

 

 

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Anonymous ,

    Yes, the returned text would have been what I needed. But of course it works with the API.

    This is a really bad format..

    Please check it out:

     

    let
url = "https://api.socialbakers.com/1/aggregated-metrics",
body = "{
""profiles"": [{""id"": ""36144455"",""platform"":""twitter""}],
""date_start"": ""2020-07-01"",
""date_end"": ""2020-09-30"",
""metric"": ""insights_impressions"",
""dimensions"": [{""type"": ""date.day""}]
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[Headers=[#"Content-Type"="application/json; charset=utf-8",
Authorization="Basic ""TWpReU1EUXlYekU0TmpreE9EaGZNVFU1TkRJeE16a3hNakU0T1Y4MllqZGtZemc1WW1Rd1lqbGxNMkUxWkdRelptRTVaREF3TnpobVpEVTRZUT09OmJjZDRlNGNmMDllMGM0M2IxNjRiYjFiMTdjNjM5ZDE3"""],
Content = Text.ToBinary(body)])),
    ParsedData = Source[data],
    Custom1 = Source,
    header = Custom1[header],
    #"Converted to Table" = Table.FromList(header, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "rows"}, {"type", "rows"}),
    ParsedMetric = #"Expanded Column1"{[type = "metric"]}[rows]{0},
    ParsedDateDays = #"Expanded Column1"{[type = "date.day"]}[rows],
    Custom2 = Table.FromColumns( { ParsedDateDays, ParsedData }, {"DateDays", "Data"}),
    #"Expanded Data" = Table.ExpandListColumn(Custom2, "Data"),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Metric", each ParsedMetric)
in
    #"Added Custom"

     

6 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi Anonymous ,

    your query requires a token and a secret to return the data.

    If you cannot share it, please execute the following code. It will convert the received API returns to a text string that you can paste here for further processing: 

     

    let
url = "https://api.socialbakers.com/1/aggregated-metrics",
body = "{
""profiles"": [{""id"": ""36144455"",""platform"":""twitter""}],
""date_start"": ""2020-07-01"",
""date_end"": ""2020-09-30"",
""metric"": ""insights_impressions"",
""dimensions"": [{""type"": ""date.day""}]
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[Headers=[#"Content-Type"="application/json; charset=utf-8",
Authorization="Basic ""######"""],
Content = Text.ToBinary(body)])),
ParseToText = Text.FromBinary( Json.FromValue(Source) )
in
ParseToText

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi

       

      ImkeF  thanks for your response! 

       

      here's the key:

       

      let
      url = "https://api.socialbakers.com/1/aggregated-metrics",
      body = "{
      ""profiles"": [{""id"": ""36144455"",""platform"":""twitter""}],
      ""date_start"": ""2020-07-01"",
      ""date_end"": ""2020-09-30"",
      ""metric"": ""insights_impressions"",
      ""dimensions"": [{""type"": ""date.day""}]
      }",
      Parsed_JSON = Json.Document(body),
      Source = Json.Document(Web.Contents(url,
      [Headers=[#"Content-Type"="application/json; charset=utf-8",
      Authorization="Basic ""TWpReU1EUXlYekU0TmpreE9EaGZNVFU1TkRJeE16a3hNakU0T1Y4MllqZGtZemc1WW1Rd1lqbGxNMkUxWkdRelptRTVaREF3TnpobVpEVTRZUT09OmJjZDRlNGNmMDllMGM0M2IxNjRiYjFiMTdjNjM5ZDE3"""],
      Content = Text.ToBinary(body)])),
      ParseToText = Text.FromBinary( Json.FromValue(Source) )
      in
      ParseToText

       

      I tried the code given but this is what I get back: 

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi Anonymous ,

        Yes, the returned text would have been what I needed. But of course it works with the API.

        This is a really bad format..

        Please check it out:

         

        let
url = "https://api.socialbakers.com/1/aggregated-metrics",
body = "{
""profiles"": [{""id"": ""36144455"",""platform"":""twitter""}],
""date_start"": ""2020-07-01"",
""date_end"": ""2020-09-30"",
""metric"": ""insights_impressions"",
""dimensions"": [{""type"": ""date.day""}]
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[Headers=[#"Content-Type"="application/json; charset=utf-8",
Authorization="Basic ""TWpReU1EUXlYekU0TmpreE9EaGZNVFU1TkRJeE16a3hNakU0T1Y4MllqZGtZemc1WW1Rd1lqbGxNMkUxWkdRelptRTVaREF3TnpobVpEVTRZUT09OmJjZDRlNGNmMDllMGM0M2IxNjRiYjFiMTdjNjM5ZDE3"""],
Content = Text.ToBinary(body)])),
    ParsedData = Source[data],
    Custom1 = Source,
    header = Custom1[header],
    #"Converted to Table" = Table.FromList(header, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "rows"}, {"type", "rows"}),
    ParsedMetric = #"Expanded Column1"{[type = "metric"]}[rows]{0},
    ParsedDateDays = #"Expanded Column1"{[type = "date.day"]}[rows],
    Custom2 = Table.FromColumns( { ParsedDateDays, ParsedData }, {"DateDays", "Data"}),
    #"Expanded Data" = Table.ExpandListColumn(Custom2, "Data"),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Metric", each ParsedMetric)
in
    #"Added Custom"