Forum Discussion

data_team_afri's avatar
data_team_afri
Frequent Visitor
7 years ago

Error: Split json web contents with api token

Hello,
This is to request help to split a json weblink
I have an json file retreived from an api web link:


Before Change: 
= Json.Document(Web.Contents("
https://api.funnel.io/api/account/v1/-account_id-/project/project_id?group_by=campaign_day&start_day=2017-01-01&end_day=2050-12-31&fields=common-cost&apiToken=12345_example")

 

After Change:

= Json.Document(Web.Contents(
"https://api.funnel.io/api/",
[
RelativePath="account/v1/-account_id-/project/project_id"
Query=
[

group_by="campaign_day",
start_day="2017-01-01",
end_day="2050-12-31",
fields="common-cost",
apiToken="12345_example",

]
]
))



Error message: token comma expected

I thought it could be an issue with the date formate. But I still get the same error after removing the two lines.

Reason for wanting change: 
to help power Bi service recognize the link for authentication

 

References:
1) http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

2) https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Hi data_team_afri,

     

    Maybe you can mofidy the query like this and check if this can work.

    = Json.Document(Web.Contents("https://api.funnel.io/api/",
[
RelativePath="account/v1/-account_id-/project/project_id"
Query=
[
group_by="campaign_day",
start_day=Date.FromText("2017-01-01"),
end_day=Date.FromText("2050-12-31"),
fields="common-cost",
apikey="12345_example",
]
]
))

    Regards,

    Jimmy Tao

  • data_team_afri's avatar
    data_team_afri
    Frequent Visitor
    7 years ago

    Here are the corrected web contents which work on the power BI desktop. (there was a comma missing after the relative path.
    Refresh successful in desktop but fails to authenticate on the power BI service.


    Failed to update data source credentials: Web.Contents failed to get contents from 'https://api.funnel.io/api/' (404): Not Found

     

    = Json.Document(Web.Contents("https://api.funnel.io/api/",
[
RelativePath="account/v1/-account_id-/project/project_id",
Query=
[
group_by="campaign_day",
start_day=Date.FromText("2017-01-01"),
end_day=Date.FromText("2050-12-31"),
fields="common-cost",
apikey="12345_example",
]
]
))

     

    • v-yuta-msft's avatar
      v-yuta-msft
      Community Support
      7 years ago

      Hi data_team_afri,

       

      The site url may not the domain or the root path of the server which can be visited. You may change the domain url and try again.

       

      Regards,

      Jimmy Tao