One or more sources currently don't support refresh.

[Anonymous]

Hi all,

New to the community, but been using Power BI for a while. I'm having a pretty major issue when it comes to scheduling refreshes on a dashboard built using Power BI Desktop. Everything works as expected in Power BI Desktop, but once published I can't use "Refresh Now" and I get the following error when I try schedule refreshes:

I have been through my data sources one by one and have discovered that the problem is coming from an API for a website called Harvest, that tracks timesheets for employees (set up using Get Data > Web). The API only allows 100 records per request, so I originally set this up by creating it with a separate function to handle the paging, but then soon discovered that functions don't allow scheduled refreshes.

I spent the last day changing the query; I found a way of creating the API request to include paging without using any functions, but having published, the scheduled refresh still isn't working.

Can anyone help, or at least suggest why this might not be working? Query below:

let
   Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/clients?access_token=ACCESS_TOKEN&account_id=ACCOUNT_ID")),
   BaseUrl = "https://api.harvestapp.com/v2/clients?access_token=ACCESSTOKEN&account_id=ACCOUNT_ID&page=",
   List = {1 .. Source[total_pages]},
   #"Converted total_pages to table" = Table.FromList(List, Splitter.SplitByNothing(), {"PageNo", "BaseUrl"}, BaseUrl, ExtraValues.Error),
   #"Changed All Columns to Text" = Table.TransformColumnTypes(#"Converted total_pages to table",{{"PageNo", type text}, {"BaseUrl", type text}}),
   #"Added Url" = Table.AddColumn(#"Changed All Columns to Text", "Url", each [BaseUrl]&[PageNo]),
   #"Removed original columns" = Table.RemoveColumns(#"Added Url",{"PageNo", "BaseUrl"}),
   #"Changed Type" = Table.TransformColumnTypes(#"Removed original columns",{{"Url", type text}}),
   #"Added Source1" = Table.AddColumn(#"Changed Type", "Source1", each Json.Document(Web.Contents([Url]))),
   #"Removed Columns" = Table.RemoveColumns(#"Added Source1",{"Url"}),
   #"Expanded Source1" = Table.ExpandRecordColumn(#"Removed Columns", "Source1", {"clients"}, {"clients"}),
   #"Expanded clients" = Table.ExpandListColumn(#"Expanded Source1", "clients"),
   #"Expanded clients1" = Table.ExpandRecordColumn(#"Expanded clients", "clients", {"id", "name", "is_active", "address", "created_at", "updated_at", "currency"}, {"id", "name", "is_active", "address", "created_at", "updated_at", "currency"}),
   #"Removed Columns1" = Table.RemoveColumns(#"Expanded clients1",{"address", "created_at", "updated_at"})
in
   #"Removed Columns1"

Thanks,

Mike

[v-jiascu-msft]

Hi @Anonymous,Preview

Did you still get the error message after you made some changes?

What did this step do? Why retrieve the source again? This step could be the cause.

 #"Added Source1" = Table.AddColumn(#"Changed Type", "Source1", each Json.Document(Web.Contents([Url]))),

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft

Thanks for the reply. I get the error message about the data source not supporting scheduled refreshes in both versions of the query. The first version was obvious - I was using functions, which having researched into the reasons scheduled refreshes aren't supported, functions are top of the list.

The code I posted above was the new version, without using functions, so I was hoping it would work, but it still won't allow a refresh online.

I'll talk you through each part to explain why I built it like I did:

Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/clients?access_token=ACCESS_TOKEN&account_id=ACCOUNT_ID")),

Above is the API source. When it's run I receive an overview of how many records have been found, and how many pages the records span over.

BaseUrl = "https://api.harvestapp.com/v2/clients?access_token=ACCESSTOKEN&account_id=ACCOUNT_ID&page=",

Above I am setting the API URL including "&page=" to a BaseUrl parameter for use later on.

List = {1 .. Source[total_pages]},

Here I am setting up a list of 1 to the total number of pages retrieved by the API Source call.

#"Converted total_pages to table" = Table.FromList(List, Splitter.SplitByNothing(), {"PageNo", "BaseUrl"}, BaseUrl, ExtraValues.Error),
#"Changed All Columns to Text" = Table.TransformColumnTypes(#"Converted total_pages to table",{{"PageNo", type text}, {"BaseUrl", type text}}),

The first line in the above snip converts the List into a table, adding in the List numbers into one column (PageNo) and the BaseUrl into a second column (BaseUrl). The second line converts the two columns into text, ready to be combined in the next step.

#"Added Url" = Table.AddColumn(#"Changed All Columns to Text", "Url", each [BaseUrl]&[PageNo]),
#"Removed original columns" = Table.RemoveColumns(#"Added Url",{"PageNo", "BaseUrl"}),

The first line above combines the BaseUrl and PageNo into a new column (Url). This gives me a new column of Urls, where each row points to each page in the API. The second line just removes the original two columns, BaseUrl and PageNo, to simplify what's on screen.

#"Added Source1" = Table.AddColumn(#"Changed Type", "Source1", each Json.Document(Web.Contents([Url]))),

This is the line you asked about. I created a new column (Source1) to query the Url on each line. This gives me a column of Records, each one containing a list of all records retrieved on that page of the API. If there is a better way of doing this I'm more than happy to change it, but it was the only way I could figure out how to do it!

#"Removed Columns" = Table.RemoveColumns(#"Added Source1",{"Url"}),
#"Expanded Source1" = Table.ExpandRecordColumn(#"Removed Columns", "Source1", {"clients"}, {"clients"}),
#"Expanded clients" = Table.ExpandListColumn(#"Expanded Source1", "clients"),
#"Expanded clients1" = Table.ExpandRecordColumn(#"Expanded clients", "clients", {"id", "name", "is_active", "address", "created_at", "updated_at", "currency"}, {"id", "name", "is_active", "address", "created_at", "updated_at", "currency"}),

The final four lines do the following: I remove the original Url column, then I expand the Record in the Source1 column to display a List of the clients column from the API, then I expand the List to show a new row for every client retrieved, and finally I expand out the Records to show all the columns for each client, giving me all the detail.

Is there a better way do perform the step I did in the #"Added Source1" step, that might allow me to run scheduled refreshes?

Thanks,

Mike

[Anonymous]

Hi @v-jiascu-msft,

Just wondering if you've had any thoughts on the above?

Thanks,

Mike