Formula.Firewall in Power Query

[jaryszek]

Hi Guys,

how are you workarounding issue with formula firewall while creating Power Query queries?
This is well known issue but there is no one solution.

How can i handle this?
In attachment very simple example where problem occurs.


Thank you for help,
Jacek

[Olly]

Enable Fast Combine in the workbook.

You can do this using VBA:

Please Login or Register  to view this content.

Fast Combine sets the workbook to ignore the privacy levels for each source - and it applies to all users of the workbook.

More information available here: https://blogs.technet.microsoft.com/...in-excel-2016/

[jaryszek]

Thank you Olly!!

Much appreciated.

so you are also using this on workbook open event?

Best,
Jacek

[Olly]

Not necessary - it's a property of the workbook which applies to all users, so you just need to set once.

It's worth understanding methods of design to avoid this issue, though - Ken Puls has written an excellent article about this: https://www.excelguru.ca/blog/2015/0...a-combination/

[jaryszek]

Once again thank yuo very much my friend!

Best,
Jacek

[jaryszek]

In my example,

it is possible to avoid this error directly in PQ?
I am using nice functions and code is simple because of that.

Jacek