Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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:
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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...
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"
]
}
]
}
{
"success": true,
"header": [
{
"type": "profile",
"rows": [
{
"id": "564919357",
"platform": "twitter",
},
{
"id": "164929129743",
"platform": "facebook"
}
]
},
{
"type": "metric",
"rows": [
"page_posts"
]
}
],
"data": [
[
3
],
[
6
]
]
}
Thanks
Sonia
sorry... i realise that profile is not in the list of dimensions I request...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.