Parsing out JSON query

[Anonymous]

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!

[ImkeF]

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]

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 · Accepted solution]

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"

[smarguin]

Hello

This is brilliant thanks!

I would like to build on this and add the detail by social profile. I have added this.

Parsed_JSON = Json.Document(body),

profiles1 = Parsed_JSON[profiles],
#"Converted to Table" = Table.FromList(profiles1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "platform"}, {"id", "platform"}),

It gives me the list of accounts. Now how do I link the accounts I post to the performance results of the query? Is it actually possible? 

I looked at the API (V2) and the response is supposed to provide the accounts as an answer but I dont see them anywhere...

Example request

POST /2/aggregated-metrics HTTPS
Host: api.socialbakers.com
Authorization: Basic base64_encoded_auth
Content-Type: application/json; charset=utf-8

{
  "profiles": [
    {
      "id": "564919357",
      "platform": "twitter",
    },
    {
      "id": "164929129743",
      "platform": "facebook"
    }
  ],
  "date_start": "2018-11-01",
  "date_end": "2018-11-12",
  "metric": "page_posts",
  "dimensions": [
    {
      "type": "profile"
    }
  ],
  "filter": [
    {
      "field": "post_labels",
      "value": [
        "e3af7de2d2274393b86b"
      ]
    }
  ]
}
    

Example response

{
  "success": true,
  "header": [
    {
      "type": "profile",
      "rows": [
        {
          "id": "564919357",
          "platform": "twitter",
        },
        {
          "id": "164929129743",
          "platform": "facebook"
        }
      ]
    },
    {
      "type": "metric",
      "rows": [
        "page_posts"
      ]
    }
  ],
  "data": [
    [
      3
    ],
    [
      6
    ]
  ]
}

Thanks 

Sonia

[smarguin]

sorry... i realise that profile is not in the list of dimensions I request...

[Anonymous]

Yes! Perfect thank you 🙂 @ImkeF