How to create a dynamic query to sharepoint?

Petri P 21 Reputation points
2021-03-09T10:28:44.587+00:00

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.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,273 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,755 questions
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2021-03-09T20:17:21.517+00:00

    Hi @Petri P

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

    75879-demo.png

    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


0 additional answers

Sort by: Most helpful