Use parameter in odbc Query

Power Query
1

Hi there,

Database: Pervasive version 13

Based off a selection in an excel spreadsheet, I’d like my SQL in Power Query to be modified. The commented out section of query where I hardcode “108” works. Now, I need to get it working with a PPeriod_parameter. The error I get is directly below and seems to occur quite frequently if google is anything to go by. Does anyone have a suggestion as to how I can fix this error? In truth, I’m actually quite confused as to what the error even means.

err

screenshot
screenshot1205×884 85.5 KB

As always, thank you.
Michelle

2

Hi @michellepace

  1. The Step-1 (PPeriod_parameter ) output should be like below.

image
image1700×342 14 KB

  1. And in Step-2(Source) please add double quote at end of the statement with & sign below for your reference.
    where PPeriod = " & PPeriod_parameter & ""]

image

Make sure your PPeriod_parameter should be of text as you want to input 108 which it will consider as number and you will get error then in that case you can use Text.From formula as below.

image

3

Hi Mukesh,

Thank you. Works like a charm. This is what my final M-code looks like:

let
    pperiod = Excel.CurrentWorkbook(){[Name="selected_pperiod"]}[Content]{0}[Column1],
    pperiod_text = Text.From(pperiod),
    Source = Odbc.Query("dsn=PASTEL.odbc", "select * from LedgerTransactions where PPeriod = " & pperiod_text)
in
    Source
1 Like