New to Power Query Need to refence a cell for SQL query

**BSWhipp** · 05-08-2018, 09:49 AM

I have a power query to a SQL database that works fine as stated below.
let
Source = Sql.Database("GVL03", "M2MData01", [Query="select frcpdate from rcmast where frcpdate > 01/01/1900)"])
in
Source

What I want to do is reference a cell and replace the last part "Cast('01/01/1900'... With a date they put into a cell.

I have created a table with the date in it and is in the Power Query and works

then have tried to update the first Query Code with

Source = Sql.Database("GVL03", "M2MData01", [Query="select frcpdate from rcmast where frcpdate > "
& Excel.CurrentWorkbook(){[Name="BeginDate"]}[Content]{0}[BeginDate])])

I get Error: Expression.Error: We cannot apply operator & to types Text and DateTime

Any help would be appreciated, Thanks.

**dflak** · 05-08-2018, 01:50 PM

You can do something like this:

Please Login or Register  to view this content.

Cell B1 could contain a formula like: =TODAY() - 30 (to get last 30 days)
and Cell B2 is = Text(B1,"dd/mm/yyyy")

**BSWhipp** · 05-08-2018, 02:26 PM

Confused. I can do a Dim statement inside a Power Query?

**dflak** · 05-08-2018, 05:17 PM

My apologies. I assumed that this was part of a macro.

**sandy666** · 05-08-2018, 05:54 PM

maybe try to define parameter, eg.
NR is a number (in this case this is a year) but whole string is a text so change number to text

Please Login or Register  to view this content.

this is example only so adapt it to your needs

**sandy666** · 05-08-2018, 07:04 PM

or read this: Import Data from Database using Native Database Query (Power Query)

**Norie** · 05-09-2018, 03:12 AM

Is BeginDate a named range with the criteria you want to use in the query?

**BSWhipp** · 05-09-2018, 08:50 AM

I will try to figure this out. If you could explain the let statements to me it might be helpful. It appears I can have several lets yes?

**Norie** · 05-09-2018, 09:07 AM

This should actually be straightforward but I think we need to know what BeginDate actually refers to.

Is it a column in a table that refers to multiple values?

Is it a single value in a cell?

**sandy666** · 05-09-2018, 09:14 AM

You can try with parameters:
https://www.youtube.com/watch?v=L0Y1KL7o3aQ
or
https://community.powerbi.com/t5/Des...rs/td-p/345947
or
https://blog.learningtree.com/creati...mula-language/
or:
Using dynamic parameter values in Power Query Queries

**BSWhipp** · 05-09-2018, 01:53 PM

OK, I will investigate tomorrow. Leaving for the day. Have to reboot servers tonight.

**sandy666** · 05-09-2018, 06:20 PM

you can test:

before

Please Login or Register  to view this content.

after

Please Login or Register  to view this content.

not tested!!! but maybe it will give you the right way

I defined parameters randomly so don't laugh

**Norie** · 05-09-2018, 06:42 PM

Have you tried formatting the date parameter, i.e. Excel.CurrentWorkbook(){[Name="BeginDate"]}[Content]{0}[BeginDate])] here?

Source = Sql.Database("GVL03", "M2MData01", [Query="select frcpdate from rcmast where frcpdate > "
& Excel.CurrentWorkbook(){[Name="BeginDate"]}[Content]{0}[BeginDate])])

You could use Power Query's Date.ToText for that.

Source = Sql.Database("GVL03", "M2MData01", [Query="select frcpdate from rcmast where frcpdate > "
& Date.ToText(Excel.CurrentWorkbook(){[Name="BeginDate"]}[Content]{0}[BeginDate])], "yyyy-mm-dd"))

**Norie** · 05-09-2018, 07:55 PM

Not pretty but it worked for me.

Please Login or Register  to view this content.

**BSWhipp** · 05-10-2018, 08:47 AM

NORIE YOU ARE A GENIUS!!!! Thank you so much. I can code in VBA and VB but I have never worked in Power Query until now. Thank you Thank you Thank you. And to all others, Thanks for the training sessions.

**Norie** · 05-10-2018, 09:00 AM

I've only really started using Power Query and it seems quite complicated for some things.

I mean look at what I needed just to get the date from a cell as a string in an acceptable format.

PS Bet there's a far simpler way to do it, not found it yet though.

New to Power Query Need to refence a cell for SQL query

LinkBack

Thread Tools

Rate This Thread

Display

New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Re: New to Power Query Need to refence a cell for SQL query

Thread Information

Users Browsing this Thread

Similar Threads

Sub-Forum for Power Query & Power BI?

VBA Macro Power Query with weblink and name from two cell value

Modifications in Power Query not updating in Power BI Service linked via Local Drive Excel

error in power query & power pivot

[SOLVED] How can I make a cell truly blank when doing a Power Query import from CSV?

Power Query, Native Query Paramter

MS Query to Power Query

Bookmarks

Bookmarks

Posting Permissions