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