Very awesome and insightful video, thank you for posting this and I am adding your book to my amazon cart now. I need to learn more about the formula firewall error(s) so I might become better at avoiding them. It is one frustrating error message especially when you think you have worked through the correct steps to avoid it showing up.
Looks like Chapter 15 in my copy of the Definitive Guide to Power Query, is going to be well thumbed as I refer back to it often. Thanks for this video highlighting it.
Also, i knew the second approach, which is annoying in long term, but that function trick.. hiw you even discover these things? My API calls will get a boost today:)
That conversion to a function, this work only once 😢 i tried it nested and it failed. First I'm getting a token from one source, then calling API with that token. Thanks to your trick it worked, but then when i want use this as a source for next call then it throws the error.
Hi Rick, nice hacks! ❤ Does your book teach the necessary skills to write a Power BI custom connector including bearer token web API authentication protocol in M?
Hi Rick! You just did an exhaustive list of methods to bypass the firewall security. So does it really make sense for the PQ developers to consider different partitions for security when in fact you can encapsulate everything in the same partition? Kinda... makes no sense to me? Since I don't have the option to conect to databases (sql or not) I just change the global query settings in Excel so that I don't get the firewall message. Would this "firewall thingy" be the only reason to prefer working with native parameters instead of using little queries reading from tables or named ranges from the workbook?
Hey Ruben. I’m not sure this is an exhaustive list, but the provided methods are very effective. So you asked if it makes sense for developers to consider different partitions. For an in depth analysis of partitions read this: learn.microsoft.com/en-us/power-query/data-privacy-firewall#partitions-in-depth If you ask me, I’d say the topic is only for those who really want to delve in deep into the topic. For all others, you don’t need to understand partitions deeply. I just wanted to give you an idea of what’s happening. Are we bypassing security by encapsulating everything in the same query? No we’re not. We just made sure we put things in the same partition. What comes next is the formula firewall error for data compatibility. It comes up in risky scenario. So you will also need to bypass that one. And it’s here where we can prevent different queries from folding together. Our book covers that topic in depth so you know methods to work with it. Hope that clears things up.
@@BIGorilla just one more reason to read the book, haha! As I told Melissa, I am reading the 3 books in FIFO, so I'll need to finih Gil' first, then yours :P
where was this video 4,5 years ago :D currently I know the bypassing methods. but that time when i was searching for answers none were there, just some MS hints over the internet :D Anyway great work! Is this book availible as PDF when i buy the paper version?
Thank you! Absolutely, it would have been great if this stuff was out there before. On your question of the pdf. Yes. When you get the printed book, you can request the pdf version for free with Packt. Instructions in the book :)
Just in time. I faced this issue today at my job. Now I understand it! However, I have one more challenge - every time a user runs a Power Query that was last saved by another user, they are prompted to set the Workbook’s Privacy level. How can it be resolved?
That’s the second firewall error. It’s covered in our book, the definitive guide to power query m. But who knows I may record another video on the topic later :) Just make sure to set compatible privacy levels for both data sources.
@@BIGorilla But the problem is, if I understand correctly, that every user has to change it every time the file was last saved by another user. I would be really grateful for the video covering this topic
@@mrbartuss1 You can check Always ignore Privacy Level settings and the pop up windows will never show again. Open new blank Excel > Go to tab Data > Get Data > Query Options > Privacy > Always ignore Privacy Level settings But you have to set in for every user that is using your PQ. Not great solution, but it work.
Mag ik je nog wat vragen Rick? Ik heb mijn ING-afschriften in PowerQuery gezet en ik klungel een beetje met dubbele regels verwijderen. Alleen de kolommen selecteren en dan in het menu dubbele rijen verwijderen werkt niet. Want als ik met opzet een jaar dubbel download dan komen die ook dubbel in mijn overzicht terug. Ik heb op internet een commando gevonden: #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"DSI"}), En iets met Table.Buffer. Is dat het juiste commando? Ik weet alleen niet waar die DSI naar verwijst. Thanks again. Heb je een bitcoinadres?
Hoi Ferdi, In het voorbeeld verwijderd de formule genaamd DSI. Dat kan prima werken maar het hangt af van welke conditie je precies zoekt. Door deze formule wordt de eerste unieke regel behouden, alle overigen worden gedelete. Je kunt wat meer voorbeelden vinden op: powerquery.how/table-distinct/
To learn more about the Formula Firewall, check out our new book: The Definitive Guide to Power Query M: geni.us/ODZl8
I was not aware of the function workaround for this. Great ! Thanks !!!
Very awesome and insightful video, thank you for posting this and I am adding your book to my amazon cart now.
I need to learn more about the formula firewall error(s) so I might become better at avoiding them.
It is one frustrating error message especially when you think you have worked through the correct steps to avoid it showing up.
Thanks for the demonstration. I bought your book and am halfway through it now. Great material. Thanks for all your work!
Ziggle - so glad to hear that. I think you'll like Chapter 15 where this is discussed!
Looks like Chapter 15 in my copy of the Definitive Guide to Power Query, is going to be well thumbed as I refer back to it often. Thanks for this video highlighting it.
Excellent! Glad you like it Grainne!
Also, i knew the second approach, which is annoying in long term, but that function trick.. hiw you even discover these things?
My API calls will get a boost today:)
Ok, but what's the problem. MS wanted to prevent something, how these changes make a difference in terms if the risk MS wanted to avoid?
That conversion to a function, this work only once 😢 i tried it nested and it failed.
First I'm getting a token from one source, then calling API with that token. Thanks to your trick it worked, but then when i want use this as a source for next call then it throws the error.
Hi Rick, nice hacks! ❤ Does your book teach the necessary skills to write a Power BI custom connector including bearer token web API authentication protocol in M?
Hi Rick! You just did an exhaustive list of methods to bypass the firewall security. So does it really make sense for the PQ developers to consider different partitions for security when in fact you can encapsulate everything in the same partition? Kinda... makes no sense to me? Since I don't have the option to conect to databases (sql or not) I just change the global query settings in Excel so that I don't get the firewall message. Would this "firewall thingy" be the only reason to prefer working with native parameters instead of using little queries reading from tables or named ranges from the workbook?
Hey Ruben.
I’m not sure this is an exhaustive list, but the provided methods are very effective.
So you asked if it makes sense for developers to consider different partitions. For an in depth analysis of partitions read this: learn.microsoft.com/en-us/power-query/data-privacy-firewall#partitions-in-depth
If you ask me, I’d say the topic is only for those who really want to delve in deep into the topic. For all others, you don’t need to understand partitions deeply. I just wanted to give you an idea of what’s happening.
Are we bypassing security by encapsulating everything in the same query? No we’re not. We just made sure we put things in the same partition.
What comes next is the formula firewall error for data compatibility. It comes up in risky scenario. So you will also need to bypass that one. And it’s here where we can prevent different queries from folding together.
Our book covers that topic in depth so you know methods to work with it. Hope that clears things up.
@@BIGorilla just one more reason to read the book, haha! As I told Melissa, I am reading the 3 books in FIFO, so I'll need to finih Gil' first, then yours :P
Thanks :)) It is helpful
where was this video 4,5 years ago :D
currently I know the bypassing methods. but that time when i was searching for answers none were there, just some MS hints over the internet :D
Anyway great work!
Is this book availible as PDF when i buy the paper version?
Thank you! Absolutely, it would have been great if this stuff was out there before.
On your question of the pdf. Yes. When you get the printed book, you can request the pdf version for free with Packt. Instructions in the book :)
Just in time. I faced this issue today at my job. Now I understand it! However, I have one more challenge - every time a user runs a Power Query that was last saved by another user, they are prompted to set the Workbook’s Privacy level. How can it be resolved?
That’s the second firewall error. It’s covered in our book, the definitive guide to power query m.
But who knows I may record another video on the topic later :)
Just make sure to set compatible privacy levels for both data sources.
@@BIGorilla But the problem is, if I understand correctly, that every user has to change it every time the file was last saved by another user. I would be really grateful for the video covering this topic
@@mrbartuss1 I’d have to do some testing to see the happening. It doesn’t ring a bell at the moment
@@mrbartuss1 You can check Always ignore Privacy Level settings and the pop up windows will never show again.
Open new blank Excel > Go to tab Data > Get Data > Query Options > Privacy > Always ignore Privacy Level settings
But you have to set in for every user that is using your PQ.
Not great solution, but it work.
Is there a way to do a range join or non equi join in Power Query ?
Absolutely - have a look here: ruclips.net/video/4eZAG_K4R54/видео.html
Mag ik je nog wat vragen Rick? Ik heb mijn ING-afschriften in PowerQuery gezet en ik klungel een beetje met dubbele regels verwijderen. Alleen de kolommen selecteren en dan in het menu dubbele rijen verwijderen werkt niet. Want als ik met opzet een jaar dubbel download dan komen die ook dubbel in mijn overzicht terug. Ik heb op internet een commando gevonden:
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"DSI"}), En iets met Table.Buffer.
Is dat het juiste commando? Ik weet alleen niet waar die DSI naar verwijst. Thanks again. Heb je een bitcoinadres?
Hoi Ferdi,
In het voorbeeld verwijderd de formule genaamd DSI. Dat kan prima werken maar het hangt af van welke conditie je precies zoekt.
Door deze formule wordt de eerste unieke regel behouden, alle overigen worden gedelete. Je kunt wat meer voorbeelden vinden op:
powerquery.how/table-distinct/
Спасибо за видео. Хороший кейс. Жаль, что в России книга не доступна.
Can you buy the digital version ? Packt sells it :)
First ❤
O yes o yes!