Forum Discussion

Mat42's avatar
Mat42
Resolver I
5 years ago
Solved

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 = SharePo...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

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's avatar
Mat42
Resolver I
5 years ago

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!!