Power Query to use a cell reference as a filter

[Trebor76]

Hi there,

I have this line in my Power Query which is returning the desired value from a single cell named "ProcessDate_From':

strDateFrom = Text.From(Excel.CurrentWorkbook(){[Name="ProcessDate_From"]}[Content]{0}[Column1])

But I can't it to work using this ("ProcessDate" is the field name the SQL Db)...

[ProcessDate] = [strDateFrom]

...but this works fine:

[ProcessDate] = '2023-09-01'

(note this part of larger M code)

Any advice would be greatly appreciated.

Regards,

Robert

 

[RoryA]

Why are you using [strDateFrom] rather than strDateFrom, and how/where are you actually using that test?

 

[Trebor76]

Hi Rory,

Thanks for the reply.

I was using square brackets out of habit as that's I reference fields in a SQL statement. Even without them I still get an "Invalid column name 'strDateFrom'" error message.

I use the variable in a M statement like "AND [Source] <> 'Rates' AND [ProcessDate] = strDateFrom". If it were VBA I'd use this:

AND [Source] <> 'Rates' AND [ProcessDate] = '" & strDateFrom & "'"

Hope that helps.

Robert

 

[RoryA]

You’d do the same in PQ assuming that’s part of a SQL string

 

[Trebor76]

Ah thanks Rory seems to have done it. I should have used the KISS principal.

I need to format the date variables and I'm trying this but it keeps saying the function expects three arguments but I've given one:

strDateFrom = Date.ToText(#date(Excel.CurrentWorkbook(){[Name="ProcessDate_From"]}[Content]{0}[Column1]),"YYYY-MM-DD")

Do you know what I'm missing?

Many thanks

 

[Trebor76]

Actually this seems to work:

strDateFrom = Text.From(Excel.CurrentWorkbook(){[Name="ProcessDate_From"]}[Content]{0}[Column1],"en-CA")

Thanks Rory