Forum Discussion

AbhinavJoshi's avatar
AbhinavJoshi
Responsive Resident
2 years ago
Solved

Reference a query in the source step

Hello,

 

I would like to reference a query or a parameter in the source step. I am using sharepoint as my data source. I would like to get the current month and year value that changes every month (dynamically) in my source step. The format I would like Year-Month (Ex: 2023-10)

 

What I would like to achieve 

 

= Excel.Workbook(Web.Contents("www.mysharepoint.com" +  "Current Month and Year Parameter" + "FileName"), null, true)

 

I have made a query that gives me the value but I don't no how to reference it in the source step, here is the code for query.


let
currentDate = Date.From(DateTime.LocalNow()),
Custom1 = {currentDate},
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type text}}, "en-CA"), "Column1", Splitter.SplitTextByPositions({0, 7}, false), {"Column1.1", "Column1.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Column1.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1", "Date"}}),
Custom2 = {#"Renamed Columns"}
in
Custom2

 

Thank you in advance for the help,

Abhinav

2 Replies

  • AbhinavJoshi's avatar
    AbhinavJoshi
    Responsive Resident

    Thanks amitchandak . It intially gave me an error: We cannot convert the value #datetime(2023, 10, 18, 11, 6, 4.7513611) to type Date. But I passed Date.ToText(Date.From(DateTime.LocalNow())) instead of Date.ToText(DateTime.LocalNow(), "yyyy-MM")

    Have a great day!