Powerful trick to get data from any folder in Excel (works even after renaming!)

Power Query is great for automation, until it doesn't work because someone (I am not naming them) goes ahead and renames a file or the folder!
Then you witness the slow collapse of your automation domino empire.
In this video, let me share with you a simple and elegant fix to the problem.

Using parameters!

Learn how to automate tasks effectively with Power Query using dynamic folder paths.

⏱ video topics:
=============
0:00 - Power Query Automation Error (file renamed ⚠)
0:13 - Creating file name & folder path parameters
2:07 - Loading the data and parameterizing the path
3:46 - Fixing the firewall warning error
4:32 - Close and Load customization to get just data (not parameters)
5:11 - Testing with new data
6:03 - What if you want to combine all the data instead?

📁 combine datasets with Power Query
================================

~

Power Query Crash Course 🎁
=========================

#excel #automation

[sandeeppaib]

Thank you so much for the wisdom Chandoo... I was literally searching for this from 8 months... You are a gem😍😍

[TSSC]

A comment about changing the privacy level (4:13): this should most probably be set in the *current* *workbook* section.
Changing the global Settings affects _all_ workbooks, and may result in negative effects (hence the firewall warning)!

• On your private computer …
Do what you want.
• On your employer’s hardware …
Don’t change the global setting if you aren’t sure you are allowed to.

[Everyonelovesyou]

There is a downside! and that is you keep getting "no data source" error within power query, since the mini table containing file and folder path do not auto update when the workbook is moved to another folder or even on a different machine.
Then you have to recopy and paste the path> go to PQ and refresh. Is there a way round from Chandoos.
You are always helpful in any case..

[kadem]

To make it even more dynamic, use =Cell("filename") for filename and folder path. That way, if you send the folder and master sheet to someone in a zip folder, the query wont break.

[calebhunyadi]

Where was this video 5 days ago? You very clearly laid out what I spent several hours trying to figure out.

[CollinReinking]

This is nice but I prefer workflows that load from a single file in as folder. This way a user just needs to move the files, no renaming and no editing a field in excel.

[alex]

If someone changes the table name. Also BOOOMMM !
And also if someone changes a column name BOOOMMM !

[phyohtetpaing]

That's helpful but how about for the multiple files? And also instead of adding "/" in the table, you can use in the formula bar like this "= Excel.Workbook(File.Contents(folder_path&"\"&file_name), null, true)".

[maciejkopczynski]

Is there a workaround for turning query privacy level settings off? Any way to keep it turned on but let Organizational queries refer other Organzational level queries? I would love to watch a video tutorial on Data Privacy Settings and various formula firewall errors we can encounter. Awesome as always! Thank You Chandoo!

[MiguelRodriguez]

Nice! I’ve been doing this for a while with workflows others will use. It’s almost dummy proof!

[narasimhareddy]

Hi Chandoo, I have created a matrix in powerbi with business unit in rowws year in columsn and revenue and loss in valies, how can add sepeare section/column which has variance/dofferece of merics in those columns, if i select 2023, 2024 i should get values for those tel yrsrs and also variance section should show difference of two years. Please reply

[thagmets]

Please, what is the best slide size for data show presentation?

[Metalhorse_]

PROBLEM: Combining multiple files with different no. Of columns is a problem. File A has 22 cols. File B has 34 cols. The combined query from a folder always ends up with 22 columns... Please make a work around video.

[mero]

how can you add a variable in pivot tables

[suvendubiswas]

In my excel, not finding that Get data option at all!!! Why??

[Kumariello]

Chandoo bolke bacha samjhe kya, data guru hu me data guru 😂