Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
Tips and Tricks
  • jeffshieldsdev's avatar
    jeffshieldsdev
    4 years ago

    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.
jeffshieldsdev's avatar
jeffshieldsdev
Icon for Solution Sage rankSolution Sage
4 years ago

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.
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • jeffshieldsdev's avatar
    jeffshieldsdev
    Icon for Solution Sage rankSolution Sage
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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/")
      • jeffshieldsdev's avatar
        jeffshieldsdev
        Icon for Solution Sage rankSolution Sage
        3 years ago

        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"