Data Source Power BI

[zameenakarmali]

Hi, 

I have a data source which has my sales data for the quarter. I make a copy of the file at the end of each week and month. 

The excel files have all have the same layouts, headers etc just the values are different. They are all being referenced in my Power BI file.

Is there a way I can create the data source without connecting to each file? Perhaps by using a parameter? 

thanks, 

[v-veshwara-msft · Accepted solution]

Hi @zameenakarmali ,

Thanks for the clarification.

The reason you are seeing duplicate tables and multiple relationships with Dim_Date is because each of your files is being loaded as a separate query. To avoid this, instead of connecting to each file, you can connect to the parent folder and append all files into a single fact table in Power Query. While doing this you can add a custom column to identify whether the data came from the weekly, monthly, or live folder. This way you only need one relationship to Dim_Date, and you can use that new column in your report to switch between weekly, monthly, and live views.

Here is the documentation on combining files from a folder: Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Learn

You may also find it useful to check these similar community discussions that go through the same type of scenario:

Solved: Power BI: How to Combine Multiple Excel Files from... - Microsoft Fabric Community

Solved: Correct way to append/merge data files - Microsoft Fabric Community

Solved: How to bring in multiple excel files once availabl... - Microsoft Fabric Community

Hope this helps. Please reach out for further assistance.

Thank you.

Also thanks to @Mauro89 and @srlabhe for addressing this earlier and for valuable guidance.

[zameenakarmali]

this was great thank you for all your help

[Mauro89]

Hi @zameenakarmali 

if I got you correct you have multiple excel files in a folder which shall be loaded into PBI. 
In that case you can load the data by not choosing the files itself as a data source but the folder itself. Depending on the data structure you might clean the data (e.g. duplicates). 

I hope this helps, if yes feel free to mark it as solution. 

Best regards!

[zameenakarmali]

Hey, 

Yes I have multiples excel files that are loaded., 

Currently I have a folder saved as monthly which lists all the Files as 202507, 202508 etc.

I have a weekly folder that saves the files under a folder for each month and the files are saved as  WE 08 08 25 ...

I then have a live version of the file

In my dashboard there is a monthly/ weekly and live view so I need to connect to all the files. 

can you let me know if this can be done with a better solution

[srlabhe]

You can load folder and try to append files , not sure if that works

[Mauro89]

Hi @zameenakarmali,

I thinks that's already a ok solution. If you have all your sales data in a single file and want to create the weekly an monthly extract of it you can also go the Power Query way to create new tables out of it. 

Best regards. 

[zameenakarmali]

Yes but the only is then Is I have duplicate tables and relationships between each of the table and the Dim_Date table 

[v-veshwara-msft · Accepted solution]

Hi @zameenakarmali ,

Thanks for the clarification.

The reason you are seeing duplicate tables and multiple relationships with Dim_Date is because each of your files is being loaded as a separate query. To avoid this, instead of connecting to each file, you can connect to the parent folder and append all files into a single fact table in Power Query. While doing this you can add a custom column to identify whether the data came from the weekly, monthly, or live folder. This way you only need one relationship to Dim_Date, and you can use that new column in your report to switch between weekly, monthly, and live views.

Here is the documentation on combining files from a folder: Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Learn

You may also find it useful to check these similar community discussions that go through the same type of scenario:

Solved: Power BI: How to Combine Multiple Excel Files from... - Microsoft Fabric Community

Solved: Correct way to append/merge data files - Microsoft Fabric Community

Solved: How to bring in multiple excel files once availabl... - Microsoft Fabric Community

Hope this helps. Please reach out for further assistance.

Thank you.

Also thanks to @Mauro89 and @srlabhe for addressing this earlier and for valuable guidance.

[v-veshwara-msft · Accepted solution]

Hi @zameenakarmali ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.