Forum Discussion

benryon1's avatar
benryon1
Microsoft Employee
6 years ago

Help using RelativePath with Web.Contents to use schedule refresh in PBI Service

I'm trying to use an API through Buzzsumo with my data model that has other sources, and I'm getting the following error when publishing to Power BI service, and attempting to schedule refresh :

 

"Unable to refresh the model (id=xxxxxxxx) because it references an unsupported data source."

 

My research has me across this article, where I need to use RelativePath to get around using Web.Contents().

 

My request is somewhat simple - I have absolutely no clue how to translate what's done in this article to the M code below, and would love guidance on what to place where:

 

 

 #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Table.AddColumn(#"Removed Columns", "Custom", each Function.InvokeAfter(()=> Json.Document(Web.Contents("https://api.buzzsumo.com/search/articles.json?q=" & [encoded_url] & "&api_key=xxxxxxxxx")),#duration(0,0,0,1)))),

 

 

I've included the entirety of the M code below for reference:

 

 

let
    Source = Excel.Workbook(Web.Contents("https://microsoft-my.sharepoint.com/personal/xxxxxxxx/Documents/cat-table-story-level%20buzz%20trial.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Page URL (No Query Strings) (v41)", type text}, {"Prefix URL", type text}, {"q=", type text}, {"encoded_url", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(#"Changed Type", "Custom", each Function.InvokeAfter(()=> Json.Document(Web.Contents("https://api.buzzsumo.com/search/articles.json?q=" & [encoded_url] & "&api_key=xxxxxxxx")),#duration(0,0,0,1)))),
    Custom = #"Added Custom"{0}[Custom],
    #"Expanded Custom" = Table.ExpandRecordColumn(Custom, "Custom", {"results"}, {"Custom.results"}),
    #"Expanded Custom.results" = Table.ExpandListColumn(#"Expanded Custom", "Custom.results"),
    #"Expanded Custom.results1" = Table.ExpandRecordColumn(#"Expanded Custom.results", "Custom.results", {"twitter_shares", "total_facebook_shares", "total_shares", "total_reddit_engagements"}, {"Custom.results.twitter_shares", "Custom.results.total_facebook_shares", "Custom.results.total_shares", "Custom.results.total_reddit_engagements"})
in
    #"Expanded Custom.results1"

 

 

Any help would be greatly appreciated. I'm at the limit of my PBI and M code experience here.