Forum Discussion
Import multiple Excel files from one SharePoint folder through dynamic parameters
Hi all,
I have been scratching my head a bit around this topic. I would please like some help on the following:
I have a folder in Sharepoint where I have multiple Excel files with the following names:
File 1 – A19@B2
File 2 – A19@B3
File 3 – A19@A2
The A19 refers to the financial year. When I am in this specific folder the financial year 'A19' will stay constant, however, the text after the '@' can change as indicated above. It can be B2, B3, A2 etc.
I can import these files one at a time through a parameter.
What I need is a parameter that will pull in all 3 my files if I indicate that I want all files in my folder with a financial year of 'A19'.
Any suggestions on this?
Thank you in advance,
Edward
Are these files the same format?
You could use:
Get Data > "Folder"Then, click on the filter icon next to file name and do a filter "Text Filter" > "Contains".
This can be a parameter or hard coded value.
You can click the double down arrow to combine all files.
There's not a great way to load individually. One hacky way, you could make a blank query, then add the step:
Source = try #"Your Folder Query Name"{0}[Content] otherwise null
then do the same:
Source = try #"Your Folder Query Name"{1}[Content] otherwise null
repeat and increase the number by 1 (it is 0 indexed, so 0 references the first item in the list and so on). Make enough queries that there will always be more than items in the list.
Appreciate your Kudos
Connect with me!
1 Reply
Are these files the same format?
You could use:
Get Data > "Folder"Then, click on the filter icon next to file name and do a filter "Text Filter" > "Contains".
This can be a parameter or hard coded value.
You can click the double down arrow to combine all files.
There's not a great way to load individually. One hacky way, you could make a blank query, then add the step:
Source = try #"Your Folder Query Name"{0}[Content] otherwise null
then do the same:
Source = try #"Your Folder Query Name"{1}[Content] otherwise null
repeat and increase the number by 1 (it is 0 indexed, so 0 references the first item in the list and so on). Make enough queries that there will always be more than items in the list.
Appreciate your Kudos
Connect with me!