Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sam_Dixey
New Member

Load Query Based on Dynamic File Name (SharePoint)

I'm looking to ensure my Power BI report pulls the latest data from a SharePoint file without having to overwrite the current sharepoint file with the latest data.

 

Each day a new file is received and saved into the same SharePoint folder with the name (________YYYYMMDDhhmmss).xlsx. This has the exact same layout (columns, tabs, etc) as the previous file, however with the latest data.


Rather than having to copy and paste into the current file, overwrite the current file, or change the load data query each day, I'm hoping there is a way for Power BI to look in the same SharePoint folder and use the latest file.

 

I am new to Power BI so would greatly appreciate any simple instructions, clearly explained. Thanks community 🙂

1 ACCEPTED SOLUTION
jeffshieldsdev
Solution Sage
Solution Sage

Steps would be like this:

  1. Use "SharePoint folder" connector to connect to the site using the site root URL.
  2. Click "Transform Data" button in preview window.
  3. Filter Folder Path to folder contain the files (will have a trailing "/").
  4. Filter Extension column to ".xlsx" .
  5. Duplicate "Name" column, and/or Replace Values in Name column to remove filter name prefix, so remaining text is "YYYYMMDDhhmmss).xlsx"
  6. Sort "Name" column Descending
  7. Click on "Binary" in first row of "Content" column to drill into the XLSX file, then drill into the desired Sheet. NOTE: the default generated Power Query code will used hard-coded named identifiers. You may want to edit this to use index or position-based identifiers instead.

View solution in original post

8 REPLIES 8
Sam_Dixey
New Member

Thanks Jeff. And to confirm, would this have the effect that each morning Power BI will pull from the latest/top report in the folder? 

Yes, it should.  Each refresh will pull the max dated file.

jeffshieldsdev
Solution Sage
Solution Sage

Steps would be like this:

  1. Use "SharePoint folder" connector to connect to the site using the site root URL.
  2. Click "Transform Data" button in preview window.
  3. Filter Folder Path to folder contain the files (will have a trailing "/").
  4. Filter Extension column to ".xlsx" .
  5. Duplicate "Name" column, and/or Replace Values in Name column to remove filter name prefix, so remaining text is "YYYYMMDDhhmmss).xlsx"
  6. Sort "Name" column Descending
  7. Click on "Binary" in first row of "Content" column to drill into the XLSX file, then drill into the desired Sheet. NOTE: the default generated Power Query code will used hard-coded named identifiers. You may want to edit this to use index or position-based identifiers instead.

Won't clicking on "Binary" result in a series of steps where the File Name is hard-coded?

Good point.  The default generated Power Query will--but you can edit it to use index-based identifiers instead of named ones.

Thank you for your confirmation! 
May I also ask how to revise the code to use index-based identifiers in the following auto-generated steps?

= #"Filtered Rows"{[Name="Products.xlsx",#"Folder Path"="https://sharepoint.com/sites/References/"]}[Content]

 

= Excel.Workbook(#"Products xlsx_https://sharepoint.com/sites/References/")

The row filtering criteria is embedded in the #"Filter Rows" step. You'll need to move this logic into step(s) before #"Filtered Rows", filtering to just one row, and then selecting it.

Something like this:

let
    Source = SharePoint.Files("https://sharepoint.com/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "Filename" and [Folder Path] = "URL"),
    Content = #"Filtered Rows"{0}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(Content)
in
    #"Imported Excel Workbook"

 

this works! thank you so much!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.