Forum Discussion
2 Parameters
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?
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.
7 Replies
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.
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
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.
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.
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.
