Search code examples
excelpostgresqlpowerquerym

Parameterize Source in Power Query when connecting to PostgreSQL DB

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.

Solution

  • 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.