cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

PowerBI Service - Scheduled Refresh

Hi 

 

I've been trying to schedule refresh a report which works perfectly on PowerBI desktop (Ignore privacy level setting).  PowerBI service  has been throwing the following error when I refresh the dataset on the service web portal

 

[Unable to combine data] Section1/WithTimeStamps/Source references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

The query in "WithTimeStamps" set is the following:

 

let
    final = Text.Combine(NotScannedAccounts[AccountId], "','"),
    Source = DocumentDB.Contents(@CosmosURL, "databasename", "configdata", [Query = "SELECT VALUE root from (SELECT c.AccountId,  MAX(c.Time) as maxval FROM c where c.Type = 'prereqdata' and c.AccountId in ('"&final&"') group by c.AccountId) as root "])
in
    Source

 

Where the final variable becomes a comma separated list of account ids that I need to pass to cosmos to get the data. The expected data is a table of record which contains an account id and the latest timestamp for a particular account.

 

Now, after spending hours on the forums everyone seems to be pointing towards the same excelguru website but that is not useful in this scenario (or atleast I am not able to properly understand how ot apply that in this situation.) Requesting the community for any help.

12 REPLIES 12
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

 

Best Regards,
Winniz

Anonymous
Not applicable

Hey so the problem didn't get solved based on the documents that were shared here but I actually reimplemented the entire report and ended up running in this permission issue fiasco at the very end but this helped me realise what the actual problem was. I will try my best to explain it here so here goes.

 

So let's say we have a table (T1) with certain details like account id, license id, license name etc. Now the issue occurs since I am calling cosmos for each row. This for each on each row calls the following query:  "select * from c where c.Type = 'doctype' and c.AccountId = '"&[AccountId]&"' "

 

Now this works flawlessly for my case when no privacy level check is done but causes problems when I try to refresh the report on power bi service

Hi @Anonymous ,

 

I tested and found that there are two ways to refresh successfully.

 

1. Manually enter the NotScannedAccounts[AccountId] column you quoted in the query, as shown below. But this will make the AccountID value fixed.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccountId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AccountId", type text}})
in
    #"Changed Type"

 

 

let
    final = Text.Combine(EnterTable[AccountId], "','"),
    Source = DocumentDB.Contents("https://xxxxxx", "ToDoList", null, [Query = "SELECT VALUE root from (SELECT c.id, MAX(c._ts) as maxval FROM Items c where c.id in ('"&final&"') group by c.id ) as root "]),
    #"Expanded Document" = Table.ExpandRecordColumn(Source, "Document", {"id", "maxval"}, {"Document.id", "Document.maxval"})
in
    #"Expanded Document"

 

 

2. Import all the data into PowerBI, and then use parameters or manual or DAX to filter the data.

 

image.png

 

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexDantin
Helper I
Helper I

I have several datasets that were working fine, now suddenly all my credentials were invalid and getting several different error messages like the above when trying to refresh using the Personal Gateway (which new version has come out and I have uninstalled and re-installed) -

Underlying error code-2147467259
Underlying error messageInformation is needed in order to combine data
DM_ErrorDetailNameCode_UnderlyingHResult-2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources[{"kind":"Odbc","path":"dsn=[connect_string]"},{"kind":"File","path":"O:\\[file_info].xlsx"}]
Microsoft.Data.Mashup.MashupSecurityException.ReasonPrivacySetting
Cluster URIWABI-US-NORTH-CENTRAL-H-PRIMARY-redirect.analysis.windows.net
Activity ID1cff351d-839d-462f-ba04-694ea2365b7a
Request ID7014232d-23bc-7cc8-87a7-bde4968496e6
Time2021-06-10 13:26:38


a prior message spoke about "need to rebuild the data source"

 

none of these were an issue previously.

none of the proscribed solutions helped. I had to adjust the connection string to our PostGRES AWS db in order to enable the refresh to occur.

I have confirmed that all the privacy settings have been set to "ignore"

@AlexDantin  - Just wanted to make sure you also keep same Privacy level (Ignore) in your Gateway data source.

v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

See if these documentations are helpful.

 

Behind the Scenes of The Data Privacy Firewall 

Connect to Azure Cosmos DB using BI analytics tools with the ODBC driver 

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

I'll look into these articles. Initil glance suggests that the second document might not be relevant but tysm. I'll get back with an update.

arvindsingh802
Super User
Super User

If you are using Gateway then make sure the Privacy level is same in Dataset and Gateway Datasource.

 

If this post helps, then please consider Accept it as the solution and give it a thumbs up

Anonymous
Not applicable

I'll check that and get back to you. 

Anonymous
Not applicable

Replying to keep this post alive. Still looking for solutions.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!