cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

PowerQuery Privacy settings not retained

Hi! I'm new to PowerQuery but not so much to Excel and struggling with a weird Privacy settings issue

I have a few workbooks completed with PowerQuery. One is simply fetching a table from another workbook with some row filtering and the other is importing a folder full of XML-s. Everything is stored on network drive.

The trouble is - I can't seem to get rid of the following Privacy warning before query is executed:

 

privacy_levels.jpg

 

It's straightfoward to: tick the "Ignore Privacy Levels..." checkbox + press Save + Save workbook. However that would only partly work. I can then refresh the query without this popup. I can also close the workbook and open it again - still no problems launching the query.

However if I open this file from PC no 2, I'm back to start and I need to tick this privacy confirmation checkmark once again. But this is the main issue: once I do that and go back to PC no 1 - it's showing the popup again - so even "adjust every PC / user once" doesn't apply here.

In addition to "Ignore privacy levels checks..." checkbox, I also tried adding Privacy Levels "Public" to the Workbook and Data sources. Under "Data sources" the "Current workbook" privacy level is shown as "None" once I open it again from a different PC (even though it was "Public" a minute ago from another PC).

Also Query Options - Current Workbook - Privacy Levels is switched back to "Combine data according to your Privacy Level settings for each source" even though I have set it to "Ignore the Privacy Levels and potentially improve performance".

 

Any help is appreciated! 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @crossover , 

As mentioned above, the individual file preferences for privacy level is saved in each computer, not with the file itself. You could try to set "ignore privacy level " in on query option or each prompt windows in each computer. You could refer to Privacy levels not saving for file but per user in file  for details. And if you still have problem, you could ask problem in this forum in above link.

Best Regards,
Zoe Zhi

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

 

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi @crossover , 

As mentioned above, the individual file preferences for privacy level is saved in each computer, not with the file itself. You could try to set "ignore privacy level " in on query option or each prompt windows in each computer. You could refer to Privacy levels not saving for file but per user in file  for details. And if you still have problem, you could ask problem in this forum in above link.

Best Regards,
Zoe Zhi

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

 

View solution in original post

Frequent Visitor

Thanks for your contribution - I'd leave this topic as non-solved for a short while as I don't really see a perfect solution here. ALTHOUGH - in a sense I was able to both solve it temporarily + got a little bit more insight on the issue at hand.

Firstly - there will be NO issues in case PowerQuery is launched "just" from Workbook X to either Workbook Y or Folder Z.

 

The issues begin once I configure a parameterized row filtering in Power Query - i.e. I have a separate single-value Table in Workbook X that I use as a value to filter rows in the query. The M-code is as following:

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Period] = Table10),

It works fine as a filtering function, however the Privacy level popups start once this is set up. The problem is solved in case I set Excel menu Data > Get Data > Query Options > Global > Privacy setting as "Always ignore Privacy Level settings" in a PC and the popups stop.

 

However I haven't seen a setup where I can use this table-linked parameterized filtering + use either "Always combine data according..." OR "Combine data according to..." (default setting) setup and avoid popups.

I can run either of those + then give Public or Organizational rights to "Current workbook" - and it would work fine, up until someone saves this file (even replicating the same security settings in the other PC).

Once this is done, Workbook Privacy level goes back to "None" in other PC-s.

 

So - Query Options - Global - Privacy - Always ignore Privacy Level settings is one setup that is not ruined by other PC-s, but I'm just hesistant to run this for well... security reasons. 

Community Champion
Community Champion

Refresh information is not stored in the file, its stored in the PC's cache.  Thats why you get this message again on the 2nd computer.

Fair enough.

I would be OK with configuring each PC individually in case the settings would actually be saved locally.

 

The issue I'm facing is that after doing the setup in the 2nd PC, settings are reset back in the 1st PC (easy to test with VNC connection to the 2nd PC). And vice versa - updating settings on the 1st PC, launches the Privacy popup next time I open the file in a second PC.

This couldn't be an expected behaviour, right?

 

For the user it's mostly just cosmetics (push a single extra button) - although still better avoided. However I'm looking to launch some scheduled VBA code, which also refresh this data connection, however this popup halts this process.

Power BI Desktop is a development tool, so the thing you are testing isn't the intended use of the product.  There is likely a change occuring in the file which is causing the first computer to recheck.

 

The expectation is that if you have a file that multiple people need to use (as consumers not developers), you publish it into the service inside a workspace and provide access using the Apps.

Thanks for the feedback. Given I'm new to Microsoft BI products, I might have also mixed up the community boards? I'm launching this only from Excel and running PowerQuery to load & transform data between two Excel workbooks. I'm not running a Power BI software.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors