Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ttran320
New Member

Power Query multiple source data question

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.

 

ttran320_0-1712097766991.png

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.

ttran320_1-1712098199022.png

 

Thank you for your assistance!

 

 

 

 

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1712108353888.png

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.

View solution in original post

2 REPLIES 2
ttran320
New Member

Thank you for your help!

v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1712108353888.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors