Folder Select set of xlsx files based on user input

[Anonymous]

Need Help on

Connect to the folder load only the files specified in the config excel. Need to write a custom function which on invocation should take the binary of the file perform sheet navigation promote headers and return the excel table.

For example: if the folder has 3 xlsx files i.e 1.xlsx, 2.xlsx, 3.xlsx and if the config file has only 1.xlsx and 3.xlsx (it should take this config as input and custom function should automatically invoke)

Note: All the files in the folder has same metadata (headers)

[edhans · Accepted solution]

You don't need a custom function for this. You can do the following:

1. Open the config file and get your file names as a single column in a table.
2. Browse to the folder that has the 3 XLS files in it.
3. Do an INNER JOIN with the table in the first step. Remove the "joined" column without expanding as it isn't necessary. The initial join did the filter for you.
4. Now do the combine operation. It will only ever act on the files after the filtering has been done. Change the config file contents, and the combine will reflect that.

[Greg_Deckler]

@ImkeF @mahoneypat @edhans 

[Anonymous]

@Greg_Deckler @edhans 

am completely new to this config file can you please help. Config will be like text file or xlsx or anything will do.

and with PBI> Getdata> folder> ??
Where to provide as inner join

[edhans]

I would use an Excel file. One file name per cell in a column.

Then either use the folder connector, or SharePoint.
I have a config file, so it may look like this:

Then I connect to SharePoint and it looks like this: (columns missing, but first few are all that are relevant. Same would apply to a local folder)

From here I merge as follows - clickiing Merge in the Power Query ribbon.

Now I have this. At this point, I just combine.

Changing the config file will change which files are kept on the next refresh.