Sharepoint refresh - 3 lists - no data

[dgwilson]

I'm not sure how to properly diagnose this and would like some pointers please.

I have a dashboard that is connected to 3 x Sharepoint lists, they all are provided from the same Office 365 tennant site.

I have published the dashboard to a workspace

I have published the Application.

All OK so far.

I configured a refresh set at 10am, 12pm, 2pm, 4pm ... ....

When I request a refresh (so even manually), I do not appear to get any errors, and the data in the dashboard gets removed! In other words I look at the dashboard page and filter list of clients is now blank.

Looking at the Credentials under Edit ... the dialog box implies that it authentication is anonynous... But back in the .pbix file my authentication is configured as "Microsoft Account"...

What should I have end-to-end to allow the scheduled data refresh to work?

And to confirm... there are no other data sources beyond the 3 x sharepoint lists.

- David

[Anonymous]

Hi @dgwilson,

Does your report contains records, is this blank issue only appears on dashboard? Can you please share you refresh history?

In addition, it sound like you are use oauth mode to link your datasources, but it not appears on power bi service data source credentials authorization mode?

If this is a case, any advanced operations in your query? Such as: append/merge, reference other queries, invoke custom functions.

Regards,

Xiaoxin Sheng

[dgwilson]

Hi @Anonymous thanks for the follow up.

Refresh history is in the image below. - I've never seen a failure.

Here's the first tab of the dashboard in PBI Desktop - as I expect to see it:

And here it is after being published as an application and refreshed.

I'm pretty sure the only reason one customer is there is becasue the selection is cached.

The source data comes from here (obviously I"ve changed xxxxxx):

SharePoint.Tables("https://xxxxxxx.sharepoint.com/sites/xxxxxxxxxxxx", [ApiVersion = 15])

Actually this might be more useful for you:

let
    Source = SharePoint.Tables("https://xxxxxxxxx.sharepoint.com/sites/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", [ApiVersion = 15]),
    #"32d9d393-f9e2-4ead-b5cc-70e94b7cefa7" = Source{[Id="32d9d393-f9e2-4ead-b5cc-70e94b7cefa7"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"32d9d393-f9e2-4ead-b5cc-70e94b7cefa7",{{"ID", "ID.1"}, {"Title", "Customer Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Logo URL", type any}}),
    #"Expanded Logo URL" = Table.ExpandRecordColumn(#"Changed Type", "Logo URL", {"Description", "Url"}, {"Logo URL.Description", "Logo URL.Url"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Logo URL",{{"Logo URL Long", type text}})
in
    #"Changed Type1"

Below is the Credentials screen when I Open it - seems it always defaults to displaying Anonymous

Credential details chosen

And from here everything is OK.

There are no advanced operations in the query. All 3 x lists are full refresh.

- David

[dgwilson]

Good news. I believe Refresh is working... and it has alwasy been working as it correctly reported.

I have managed to uncover what's going on and a difference between Desktop and Service (which requires more research).

I have a TRUE/FALSE field that is used as a primary filter. Only I've now discovered it's turned up in the Service after a refresh as 0 and -1.

For those that are interested here's how I found it. I built a diagnostic page to tell me what data was present:

Of course this told me that I had data - it didn't tell me why it wasn't being displayed...   aka filters...

Removing the page level filter and my report starteed to work (except that it was unfiltered).

So... what is this True/FALSE thing anyway... This needed some closer investigation.

Here's the load part on the Desktop... everything looks OK.

But after it's all published to the Service and refreshed... it turns up as 0 and -1 (not what I was expecting, and not what's happening in Desktop).

Lastly here is the Sharepoint definition of that field... At least now I know what's happening... I might go back to the desktop and reload the list (clean) and see what happens.

- David

[Anonymous]

So glad I found this post.

I've been having the same problem for a couple of weeks and it's been driving me crazy.  I can't tell you how many hours i've spent trying to figure it out!

I've removed the filter and hey presto, it's working 🙂

[dgwilson · Accepted solution]

And here's the follow up.

https://community.powerbi.com/t5/Issues/Yes-No-field-value-changing-based-on-how-it-is-refreshed/idi...

I can confirm this works.