Power Query multiple source data question

[ttran320]

Hello,

I'm currently working on my first Excel Power Query and would appreciate some guidance. I have four reports—spend, headcount, open jobs, and placements—that I'm formatting and manipulating to load into a report template for my client. Each month, the data will be refreshed, and consequently, the query data will be updated. Presently, I have the four reports stored in a File Folder, and the data source is pulling from this location.

My first question pertains to the monthly data updates. If I replace the existing files in the folder with new ones bearing identical file names, will the query automatically update based on the file source name? Or would you suggest I create separate file folders?

Secondly, as I'm creating these templates for approximately 20 different business lines, I'm wondering if there's a way to incorporate filters into the query. Ideally, I'd like to have a separate tab with a dropdown menu where the client can select their associated business line, resulting in the data updating accordingly.

Thank you for your assistance!

[ttran320]

Thank you for your help!

[Anonymous · Accepted solution]

Hi @ttran320 

For the first question, if you want to want to refresh the new file you need to make sure it meets the following requirements:

1.The data structure of the new file is the same as the original file.

2.If the data structure is the same as the original file,  If you rename or delete the column in the file , the query will casue error, so make sure that the column name is the same as the original file.

3.The connection mode is import , so when you modify the file, you need to refresh the data source manually in Power query, click the refresh button.

For the second question, becasue your data source is connecting by using importing mode, so you cannot incorporate filters into the query directly, if you want to implement this, you can consider to move the data source to sql server, then use direct query mode to connect the data source so that you can use dynamic paramater in power query, it can incorporate filters into the query, you can refer to the following link.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

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