How to create a dynamic query to sharepoint?

Question

Being new to Power Query, I tried to follow a few threads on changing the source location as e.g. in this thread:
https://social.technet.microsoft.com/Forums/en-US/18ac61ad-022b-4b24-9934-1c9d0cef23dd/how-can-i-create-dynamic-file-references-in-power-query?forum=powerquery

I started by creating a Table1 in excel (with a header called Parameter), built a ParameterQuery to fetch it's only column (named ParameterValue and row that contains the source file location. The cell in the excel has the following text:
Excel.Workbook(Web.Contents(""https://xxx.sharepoint.com/sites/Reports/Daily%20Distribution/Standard%20Reports/20210129/Fund_Daily.xlsx""), null, true),

Notice that I already included double quotes above. Now in Power Query, I tested the validity of the above string by copying it in the source. This one works:
Let
Source = Expression.Evaluate("Excel.Workbook(Web.Contents(""https://xxx.sharepoint.com/sites/Reports/Daily%20Distribution/Standard%20Reports/20210129/Fund_Daily.xlsx""), null, true),""), null, true)", #shared),

But if I try to get the text via the ParameterQuery or directly from Table1, either with or without Expression.Evaluate, I'm stuck. All the following trials fail:
let

Source = Excel.Workbook(File.Contents(ParameterQuery[ParameterValue]{0})),

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Parameter]{0},

let
Source = Expression.Evaluate(Excel.Workbook(File.Contents(ParameterQuery[ParameterValue]{0})),#shared),

The error in the first is illegal characters in path (tried also to vary the number of quotes), the error in the second is "cannot convert the value "Excel.Workbook(Web.C..." to type List." and the error in the third one is also illegal characters in path.

I have changed settings to 'ignore privacy' as suggested but still this does not work. The point of this is that I could modify the date part (20210129) more easily in excel.

Seems like an easy task but has taken hours so far - grateful to hear your suggestions.

Answer 1

Hi @Petri P

Just enter your variable (i.e. 20210129) in the 1st row of your Table1:

Query code:

let  
    QryParameter = Table.FirstValue(  
        Excel.CurrentWorkbook(){[Name="Table1"]}[Content]  
    ),  
    // Constructing the string here allows checking the result in the Applied Steps  
    DynamicURL = "https://xxx.sharepoint.com/sites/Reports/Daily%20Distribution/Standard%20Reports/" & Text.From(QryParameter) & "/Fund_Daily.xlsx",  
    Source = Excel.Workbook(  
        Web.Contents(DynamicURL),  
        null, true  
    )  
in  
    Source  

As you only have 1 variable/parameter to pass to the query you can replace Table1 with a Named cell in Excel - see Pass value as query parameter where the (no) difference between Table.FirstValue(...) and {[Name="Table1"]}[Content][Parameter]{0} is discussed