Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Use Cell value in Sql Query

  1. #1
    Board Regular
    Join Date
    Feb 2004
    Posts
    349
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Use Cell value in Sql Query


    Use Cell value in Sql Query
    I am trying to use a cell value(Parameter), the same as I use with Microsoft query, but it is not working.

    Example: Select * from table where Date = ?

    Using Power Query

    Defined the Criteria

    let
    Source = Excel.CurrentWorkbook(){[Name="AsOfDateCriteria"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"AsOfDate", type date}}),
    ANDCriteria01 = Record.Field(#"Changed Type"{0},"AsOfDate")
    in
    ANDCriteria01



    Sql Query

    let
    ANDCriteria01 = AsOfDateCriteria,
    Source = Sql.Database("plrs-sql", "polaris", [Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = AsOfDateCriteria"])
    in
    Source


    Error
    Message=Invalid column name 'AsOfDateCriteria'.

    The first step shows the value of 04/11/2019.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,718
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Cell value in Sql Query

    Try this:

    Add a column and then create an if statement that looks in a particular column for the date and the else statement is " ". You can then filter on the new column to only show the non blanks.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Posts
    831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Cell value in Sql Query

    I think you need to build the queryt string, like so

    Code:
    [Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = '" & AsOfDateCriteria & "'"]
    and the date might be datetime and not text so maybe even

    Code:
    [Query="SELECT * FROM Polaris.dbo.MyTable Where AsOFDate = 
    '" & Date.ToText(DateTime.Date(myDate)) & "'"]

  4. #4
    Board Regular
    Join Date
    Feb 2004
    Posts
    349
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Cell value in Sql Query

    Thanks,
    I made the change and got this error. I copied the data from the first query and pasted to the Second query and it worked.

    Formula.Firewall: Query 'Holdings' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Cell value in Sql Query

    This usually means that you query is accessing multiple sources.
    See if this blog helps you resolve the issue, https://www.excelguru.ca/blog/2015/0...a-combination/.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •