Published Report refuses to refresh when using API as source and bearer token

[Anonymous]

Hi All!

I am at my wits end; I am using Web as the data source and using Post call to API with JSON body to get and then manipualte the data into a table.

Works perfectly on my local machine but as soon as i publish i get this failure on refresh:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The column 'Column1' of the table wasn't found."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"Column1"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}}
Cluster URI: WABI-AUSTRALIA-EAST-A-PRIMARY-redirect.analysis.windows.net
Activity ID: bc8f2337-7a33-487e-859d-d340ed3c1467
Request ID: fc9598ea-a33a-e504-b6ce-301c738e02a6
Time: 2024-01-26 13:33:20Z

I dont know what else to do. Ive set everything up correctly. 

The refresh just never ends if i try using web, so i set up an on prem gateway and authenticat with web via the gateway and it at least throws up an error now; that one

Ive got the web gateway auth set to anonymous. Why wont this work?

[Anonymous · Accepted solution]

Hi Everyone!

OK I found a workaround. I will describe the problem and why it happens, and what I did

1) Incremental refresh will try and pull data for every day you want archived and refreshed 1 day at a time. So it will pass those dates into your API call

2) If you have no data on those days, the "rows" JSON will be empty

2) In my query, it expects the "rows" JSON to have data to construct the table. So we have to handle situations where it returns no data

What I did, is i added a "try" and "otherwise" to that line that tries to expand the record from the empty table i created from the "rows" JSON, and inside the otherwise, just pointed it to the "columns" JSON which will always be populated (just 1 row of the column headers)

Before Table.ExpandRecordColumn(#"Converted to Table2", "Column1", {"values"}, {"Call"})

After try Table.ExpandRecordColumn(#"Converted to Table2", "Column1", {"values"}, {"Call"}) otherwise Table.ExpandRecordColumn(Table.FromList(columns1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 

Now, this only worked for MY particular use case because of how i build out the tables from the JSON, but the main point remains when dealing with APIs and scheduled refresh: You have to handle days where there is no data. Your query has to output the same format of data regardless of if there is data or not.

Doing the above fixed my issue. I hope this helps someone who comes across this again

[Anonymous]

OK i have worked out why

the way incremental refresh works, its finding one of the days its refreshing has no data so it fails (i.e. the API call returns nothing so it cant find the column)

My follow up question is, how do i get around this? I dont want it to fail, i just want it to skip any days with no data.

Is there a way i can insert a try/catch in the M query to get around this? Its failing because its trying to convert a json in a table when there is no json data

[Anonymous]

Hi All,

Just to be clear, this only happens using incremental refresh. If i dont have that on, i can refresh the data online (but its pointless as the data isnt changing).

I have use parameters in the JSON, as well as on a column in the table. But as soon as i turn incremental refresh on and try and refresh, i get the above error. Here is how I have passed the parameters into the JSON body for the POST (its required)

Like i said works fine until i turn on incremental refresh. Anyone know why?

Anyone got any advice?