Change the table source automatically in powerBI

[Bromik]

I have three different excel files with data. Also in my dashboard I have a slicer with the names equal to the names of my excel files. The report is based on the data from one of the files. I need to dynamically insert the name of the file into the command  Excel.Workbook(File.Contents(DYNAMICALLY_INSERTED_FILE), null, true) based on the value that is selected in a slicer. So that when the user clicks on a file name in a slicer, the data in the report is pulled from the chosen file.

How can i do that?

[Greg_Deckler]

@Bromik So, not really going to work that way. Instead, import all three files and add a column for the file name. Append them together as one big table. Use the file name column in your slicer.

[Bromik]

I have different columns in my files, So i cannot append tme. Can it be possible to download different files based on the user choice and based on the file downloaded change the visuals and the dashboard? 

[v-yaningy-msft]

Hi, @Bromik 

Thanks for Greg_Deckler's reply. Unfortunately, the current version of PBI Desktop doesn't support the functionality you're looking for, and visual objects are only affected after the data is loaded into the Semantic model.

You can submit an idea for here and the product team will take note of such a need.

Related Link: New tab (powerbi.com)

Best Regards,
Yang
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

[Bromik]

Thanks for the comment.

If I do it in Power BI Service, can it be possible there?
I want to automate this process of choosing the dataset that should be used for the charts but I have a lot of tables. I am afraid it is not optimize way to upload all of them into power BI, right? 

[v-yaningy-msft · Accepted solution]

Hi, @Bromik 

It is the same in PBI Service and does not support dynamic changes to the data source. If there is a lot of data, you can try to use Direct Query or Live Connection connection method, if it still does not meet your needs, you can use Fabric Lakehouse/Warehouse in Service, which provides a better data experience.

The Difference Between Power BI Connection Types (popautomation.com)
What is Microsoft Fabric - Microsoft Fabric | Microsoft Learn

Best Regards,
Yang
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