cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
edwardvdm Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
SteveCampbell Established Member
Established Member

Re: Import multiple Excel files from one SharePoint folder through dynamic parameters

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!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

View solution in original post

1 REPLY 1
SteveCampbell Established Member
Established Member

Re: Import multiple Excel files from one SharePoint folder through dynamic parameters

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!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 128 members 1,302 guests
Please welcome our newest community members: