Formula.Firewall and Refresh problems

[Jayna]

Hi all,
I'm fairly new to Power Query -Ive created a file which combines 4 excel documents which are saved in a sharepoint folder into 1 data table, to do this I used the get data from Web option

Then I created a query from a sharepoint excel file into a pivot chart

It was originally working, i tested it and dropped new excel files into the folder and refreshed excel, it worked fine.

I then added some custom columns and filtered columns.

after a few days the refresh all stopped working, when I click on data refresh all the file freezes and excel is 'not responding'

If I refresh only the pivot chart it seems to reflect the correct information taken from the single excel file

After a couple of more days I noticed a message 'formula.firewall information about a data source is required' I amended to organizational.

Then Ive also amended all files to reflect organizational, in my workbook and global

I've also noticed in the query the source data doesn't show the updated new files, is this right?

I'm not combining the two data sets, but I would like to work from both in one workbook

Can anyone help me fix issues:
1. Fix the formula.firewall error
2. Fix the refresh all option

3. Any other issues you may think I have

[v-cgao-msft · Accepted solution]

Hi @Jayna ,

1. Turn off the data privacy firewall: This can be done by going to Data tab --> expand Get Data --> Query Options --> Privacy --> Always ignore Privacy Level settings --> OK.
Watch these videos for more detailed steps:
How to Fix Formula.Firewall Error in Power Query - YouTube
Solve the Formula.Firewall Error in Power Query - YouTube

2. Please check if Refresh All works after applying the above settings.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum