Parameterize Source in Power Query when connecting to PostgreSQL DB

Question

Using the .Net data provider for postgresql, I would like to create an excel workbook that loads some tables from a given schema. The schema is set using a named range in the excel workbook, the table names are the same for each schema.

I tried the following:

Define a query "from other sources" / "blank query" named SchemaIdParam as

let
    rng= Excel.CurrentWorkbook(){[Name="schemaid"]}[Content]
in
    rng

(the name "schemaid" is defined in the workbook.)

Define a query "from PostgreSQL db" named mytable as

let
    src = PostgreSQL.Database("xxx.myhost.com:5235", "my_database"),
    tbl = src{[Schema=SchemaIdParam,Item="mytable"]}[Data]
in
    tbl

Now this does not work. The error message states: "[Expression.Error]: no match between key and rows in table" (own translation). Yet it works if I replace SchemaIdParam by a literal value in quotation marks. Then the correct table is delivered.

Any hints how I can resolve this are very appreciated!

The reason why I want to use a named range for the schema name is that I want to programmatically, outside from excel, set the schema name. I am very open to suggestions how to do this in another way.

Alexis Olson · Accepted Answer · 2019-06-28 15:51:19Z

1

It could be a type error. Try converting the SchemaIdParam value to text as part of that query or else try

tbl = src{[Schema=Number.ToText(SchemaIdParam),Item="mytable"]}[Data]

answered Jun 28, 2019 at 15:51

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karsten W. · Accepted Answer · 2019-06-28 16:17:31Z

0

After a lot of trying, I found the answer. I had a problem defining SchemaIdParam. A working definition is:

let
    rng= Excel.CurrentWorkbook(){[Name="jobid"]}[Content],
    value = rng{0}[Column1]
in
    value

i.e., I had to reference a specific cell in the named range.

answered Jun 28, 2019 at 16:17

Karsten W.

18.6k12 gold badges74 silver badges114 bronze badges

Collectives™ on Stack Overflow

Parameterize Source in Power Query when connecting to PostgreSQL DB

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related