Dynamic Filename

[AllanBerces]

Hi good day, can anyone help me on my PQ, the name of my data source change every week.. WK12..WK13 and so on. I want it to be dynamic.Can anyone help me to edit my PQ. 

let
Source = Excel.Workbook(Web.Contents("https://company.sharepoint.com/sites/EASTWEST/Shared%20Documents/General/East%20West%20Current%20Act...WK12.xlsx"), null, true),

Thank you

[v-priyankata]

Hi @AllanBerces 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @AllanBerces 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @AllanBerces 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Akash_Varuna]

Hi @AllanBerces Please try this 

let
    CurrentWeek = "WK" & Text.PadStart(Text.From(Date.WeekOfYear(DateTime.LocalNow())), 2, "0"),
    FilePath = "https://company.sharepoint.com/sites/EASTWEST/Shared%20Documents/General/East%20West%20Current%20Act..." & CurrentWeek & ".xlsx",
    Source = Excel.Workbook(Web.Contents(FilePath), null, true)
in
    Source

[AllanBerces]

Hi @Akash_Varuna thank you for the reply, but when I try i m getting some error. below my complete editor

let
Source = Excel.Workbook(Web.Contents("https://Company.sharepoint.com/sites/EASTWEST/Shared%20Documents/General/East%20West%20Current%20Act..."), null, true),
#"Asset_Sheet" = Source{[Item="Asset",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Asset_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"WON", Int64.Type}, {"Location", type text}, {"Complex/#(lf)Outstation", type text}, {"Order Type", type text}, {"Discpline", type text}, {"Description", type text}, {"Asset Cat. Tool (ACT)", type text}, {"BSP Turnaround", type text}, {"Execution Window", type any}, {"BSP Estimate", Int64.Type}, {"Est. hrs", type number}, {"Active/#(lf)Inactive", type text}, {"FWL", type text}, {"INJECTED", type any}, {"PENDING #(lf)CI", type text}, {"DEFERRED/#(lf)CANCELLED", type text}})
in
#"Changed Type"

Thank you

[Akash_Varuna · Accepted solution]

Hi @AllanBerces Have you used the code that provided ? Maybe you have used but i couldnt see any ways could you try this please 

let
    // Get the current week number (WK12, WK13, etc.)
    CurrentWeek = "WK" & Text.PadStart(Text.From(Date.WeekOfYear(DateTime.LocalNow(), Date.FirstDayOfWeek.Monday)), 2, "0"),
    
    // Construct the full file path dynamically
    FilePath = "https://company.sharepoint.com/sites/EASTWEST/Shared%20Documents/General/East%20West%20Current%20Act..." & CurrentWeek & ".xlsx",
    
    // Load the file from SharePoint
    Source = try Excel.Workbook(Web.Contents(FilePath), null, true) otherwise 
        error "File for " & CurrentWeek & " not found. Please check the file path or week logic.",
    
    // Access the 'Asset' sheet
    #"Asset_Sheet" = Source{[Item="Asset",Kind="Sheet"]}[Data],
    
    // Promote headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Asset_Sheet", [PromoteAllScalars=true]),
    
    // Change column types
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {
        {"WON", Int64.Type}, 
        {"Location", type text}, 
        {"Complex/#(lf)Outstation", type text}, 
        {"Order Type", type text}, 
        {"Discpline", type text}, 
        {"Description", type text}, 
        {"Asset Cat. Tool (ACT)", type text}, 
        {"BSP Turnaround", type text}, 
        {"Execution Window", type any}, 
        {"BSP Estimate", Int64.Type}, 
        {"Est. hrs", type number}, 
        {"Active/#(lf)Inactive", type text}, 
        {"FWL", type text}, 
        {"INJECTED", type any}, 
        {"PENDING #(lf)CI", type text}, 
        {"DEFERRED/#(lf)CANCELLED", type text}
    })
in
    #"Changed Type"

[AllanBerces]

Hi @Akash_Varuna @v-priyankata Thank you very much for the reply. all good working as i need.