2 Parameters

[Mat42]

So, I've put together a query that uses a parameter. Currently, the basic code looks like this (ignore the capitalised sharepoint stuff, I just needed to anonymise stuff):

Source = SharePoint.Files("https://SHAREPOINTFOLDER", [ApiVersion = 15]),
    #"P_List xlsx_https://SHAREPOINTFOLDERFILELOCATION" = Source{[Name=P_List,#"Folder Path"="https://SHAREPOINTFOLDERFILELOCATION"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"P_List xlsx_https://SHAREPOINTFOLDERFILELOCATION"),
    #"January 2021_Sheet" = #"Imported Excel"{[Item="January 2021",Kind="Sheet"]}[Data],
    #"Removed Blank Rows" = Table.SelectRows(#"January 2021_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

The item called 'P_List' is my parameter. I've created a table called P_List that lists all of the spreadsheets I want to open. The code currently opens each Excel file in P_List, goes to a sheet called 'January 2021' and transforms the data there.

However, I'd like the sheet name to be dynamic, i.e. a second parameter. I'd like to create a second table that just contains a single entry (which in this case would say 'January 2021') and uses that as the sheet name. Then, when the next month rolls around, the table would automatically change to 'February 2021' and use that as the sheet name.

I've finally managed to get my head around single parameter (it took ages because I can't get this system to make sense to me), but I don't know how to get it to acccept a second parameter. I've tried adding in the table name in the same fashion as the first parameter, but it doesn't like it.

Any ideas?

[v-janeyg-msft · Accepted solution]

Hi, @Mat42 

Yes, you can do it. You can use 'DateTime.LocalNow()' function in 'name' instead of using parameter.

Try like this: 

 #"P_List xlsx_https://SHAREPOINTFOLDERFILELOCATION" = Source{[Name=Date.ToText(Date.From(DateTime.LocalNow()),"MMMM yyyy")
,#"Folder Path"="https://SHAREPOINTFOLDERFILELOCATION"]}[Content],

Reference:DateTime.LocalNow - PowerQuery M | Microsoft Docs

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mat42]

Thanks for your reply @v-janeyg-msft , it was really helpful.

The code works fine to create the right month/year code, and I've managed to adapt it to show the previous month (which I didn't specify, but I learned something while trying to adjust it), however how do I add it to the code for the query?

let
    Source = SharePoint.Files("https://SHAREPOINTSITE", [ApiVersion = 15]),
    #"P_List xlsx_https://SHAREPOINTFOLDERLOCATION" = Source{[Name=P_List,#"Folder Path"="https://SHAREPOINTFOLDERLOCATION"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"P_List xlsx_https://SHAREPOINTFOLDERLOCATION"),
    #"January 2021_Sheet" = #"Imported Excel"{[Item="January 2021",Kind="Sheet"]}[Data],
    #"Removed Blank Rows" = Table.SelectRows(#"January 2021_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

The new line of code needs to replace all January 2021s in the above code so that it opens the right tab of the spreadsheet. I've tried adding it in, but it doesn't seem to like it.

If I just copy the line in place of January 2021, it tells me there's a problem when it's alongside a #. If I enclose it in quotes the section "MMMM, yyyy") causes it a problem. The quotes around the MMMM, yyyy  interfere with the quotes at either end and the MMMM section gets underlined in red.

[v-janeyg-msft]

Hi, @Mat42 

Do you have multiple excel files with the data name type "January 2021", or just one excel file containing multiple sheet files. If it is the second case, you need to modify the code: Item="January 2021" to Item=Date.ToText(Date.From(DateTime.LocalNow()),"MMMM yyyy")

    let
    Source = SharePoint.Files("https://SHAREPOINTSITE", [ApiVersion = 15]),
    #"P_List xlsx_https://SHAREPOINTFOLDERLOCATION" = Source{​​​​​​​[Name=P_List//Date.ToText( Date.AddMonths(Date.From(DateTime.LocalNow()),-1),"MMMM yyyy")//,#"Folder Path"="https://SHAREPOINTFOLDERLOCATION"]}​​​​​​​​[Content],
    #"Imported Excel" = Excel.Workbook(#"P_List xlsx_https://SHAREPOINTFOLDERLOCATION"),
    #"January 2021_Sheet" = #"Imported Excel"{​​​​​​​[Item=Date.ToText( Date.AddMonths(Date.From(DateTime.LocalNow()),-1),"MMMM yyyy"),Kind="Sheet"]}​​​​​​​[Data],
    #"Removed Blank Rows" = Table.SelectRows(#"January 2021_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {​​​​​​​"", null}​​​​​​​))),

Note: Use this method do not need to set the parameters, but in order to be able to extract correctly, you need to ensure that all names are in the format "January 2021".

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mat42]

I fixed it!!!! It only ruddy works!!! Thank you Janey!!!

(I'm trying to stay professional, but I've been on this for days)

There was a whole other post here, but I've now fixed the issue so it's not needed.

For anyone interested, I was being a spanner. The initial answer was basically correct, but I had the wrong idea about how prefixes with # worked. The final code looks like this:

(P_List as text) =>

let
    Source = SharePoint.Files("https://SHAREPOINTSITE", [ApiVersion = 15]),
    #"P_List xlsx_https://SHAREPOINTFOLDERLOCATION" = Source{[Name=P_List,#"Folder Path"="https://SHAREPOINTFOLDERLOCATION/"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"P_List xlsx_https://SHAREPOINTFOLDERLOCATION/"),
    #"PSheet" = #"Imported Excel"{[Item=Date.ToText( Date.AddMonths(Date.From(DateTime.LocalNow()),-1),"MMMM yyyy"),Kind="Sheet"]}[Data],
    #"Removed Blank Rows" = Table.SelectRows(#"PSheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

It is now looking at the Sharepoint site, in the Sharepoint folder, and opening every file listed in P_List. The prefix (I'm not sure of what it's actually called) #"PSheet" was originally called #"January 2021" and I wasn't sure how to add the product of the code Janey gave me here. It never really occurred to me that it was named #"January 2021" because that was the name of the step in my original data transformation. I didn't know I could just rename it to something else (mainly because I'm dense).

Anyway, it's now all working the way it should.

Thanks again!!

[amitchandak]

@Mat42 , somthing similar to these ?

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

or

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

[Mat42]

Thanks for the reply @amitchandak. Sorry, I didn't actually realise anyone else had replied.

Despite not seeing them previously, those links will be really helpful, thanks.

[v-janeyg-msft]

Hi, @Mat42 

Yes, I didn't actually say that #"January 2021_Sheet" should be changed because this is the name of the step in your pq. You only need to change the name or item to make it dynamic.

Best Regards

Janey Guo