Forum Discussion

Kees's avatar
Kees
Helper I
2 years ago
Solved

Select datafile that changes monthly

Hi,

 

Although I found some some suggestion for my problem I could not find one that suits my need.

 

I have a XLSX datafile that I need in my PBI queries that changes monthly (Always in the same Sharepoint folder).;
 - Billing Cumulative_Raw Data 2023-10 v1.0 2023-10-17

next month this file will be removed and new file will be:

 - Billing Cumulative_Raw Data 2023-11 v1.0 2023-11-17

 

So the first part stays always the same: Billing Cumulative_Raw Data.

 

in the Source of my Query Setting I have now:
let
Source = SharePoint.Contents("https://fakename.sharepoint.com/sites/fakefolder/", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"Billing" = #"Shared Documents"{[Name="Billing"]}[Content],
#"Billing Cumulative_EMEA Raw Data 2023-10 v1 0 2023-10-17 xlsx" = #"Billing"{[Name="Billing Cumulative_EMEA Raw Data 2023-10 v1.0 2023-10-17.xlsx"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"Billing Cumulative_EMEA Raw Data 2023-10 v1 0 2023-10-17 xlsx"),


I'm looking for a way to select the datafile starting with: Billing Cumulative_Raw Data.

 

Any suggestions?

 

Brgds Kees

Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
Source = SharePoint.Contents("https://fakename.sharepoint.com/sites/fakefolder/", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"Filtered Rows" = Table.SelectRows(#"Shared Documents", each Text.StartsWith([Name], "Billing Cumulative"))
#"Imported Excel Workbook" = Excel.Workbook(#"Filtered Rows"{0}[Content]),

     

    I'm looking for a way to select the datafile starting with: Billing Cumulative_Raw Data.

    Your files don't start with that.  You have the region name in between.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    let
Source = SharePoint.Contents("https://fakename.sharepoint.com/sites/fakefolder/", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"Filtered Rows" = Table.SelectRows(#"Shared Documents", each Text.StartsWith([Name], "Billing Cumulative"))
#"Imported Excel Workbook" = Excel.Workbook(#"Filtered Rows"{0}[Content]),

     

    I'm looking for a way to select the datafile starting with: Billing Cumulative_Raw Data.

    Your files don't start with that.  You have the region name in between.

    • Kees's avatar
      Kees
      Helper I
      2 years ago

      Got it working!

       

      Thanks very much, making my day!

       

      Brgds Kees