Load files with different filenames into the same query and select format with a slicer

[Anonymous]

Hello everyone ! 

I am currently learning Power BI as a part of an internship, however I am stuck trying to load data the way I need it.

1.) Users have files of the same type (.xls) but with different formatting such as standard, non-standard 1, non-standard 2 (different column names, different layouts, additional columns...). So, I want the user to be able to select his format with a slicer and then to load it. I then want to be able to manipulate the data according to the selected format.

I found this indication https://community.powerbi.com/t5/Desktop/Dynamic-Data-Source-based-on-parameter/m-p/662570/highlight... I don't know how to make it possible for the user to choose the value of a parameter using a slicer in Desktop. Is it even possible ? If it isn't, I will consider programming a small software to convert every format into a standard one.

Edit : I am currently looking into templates

2.) Secondly, the filenames can vary and I would like that no matter the name of the files, they all load into the sames query (depending on the input format or not) which is then modified according to the input format. Files will be loaded one at a time, there is no need to merge them.

Is it possible to make, for example, all .xls files load into he same query instead of one query per file name ( I would like to do "Source = Excel.Workbook(File.Contents("AnyChoosenXlsFile.xls" instead of  "Source = Excel.Workbook(File.Contents("my/path/to/specs/and/date.xlsx")) ?

Thanks in advance for reading, and for any reply.

[dax · Accepted solution]

Hi @Anonymous , 

As I know, we couldn't use slicer to affect data model, we need to use "manage parameter" option to ahieve this goal. In addition, you said that the table structures are different, so I think it is hard to apply the same M code, if you have other step after source, it will prompt error when you change sheet. By  the way, it will load one sheet or source at a time instead of load all source(if you use combine, it might work, but it need your staructure are the same).

Best Regards,
Zoe Zhi

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

[Anonymous]

Hello, 

I was think about something like

-> Load the file

 |--> Adapt the columns towards a standard formats with a parameters and an if statement

 |--> All the column in the final query have the same name

-> Transform the data in the global query

Anyway thanks for you answer, it clears every doubt I had !