none
Use computed columns in related table in power query on SQLITE via odbc RRS feed

  • Question

  • In power query ( version included with exel 2016, PC ), is it possible to refer to a computed column of a related table?

    Say I have an sqlite database as follow

    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE products (
            iddb INTEGER NOT NULL,
            px FLOAT,
            PRIMARY KEY (iddb)
    );
    INSERT INTO "products" VALUES(0,0.0);
    INSERT INTO "products" VALUES(1,1.1);
    INSERT INTO "products" VALUES(2,2.2);
    INSERT INTO "products" VALUES(3,3.3);
    INSERT INTO "products" VALUES(4,4.4);
    CREATE TABLE sales (
            iddb INTEGER NOT NULL,
            quantity INTEGER,
            product_iddb INTEGER,
            PRIMARY KEY (iddb),
            FOREIGN KEY(product_iddb) REFERENCES products (iddb)
    );
    INSERT INTO "sales" VALUES(0,0,0);
    INSERT INTO "sales" VALUES(1,1,1);
    INSERT INTO "sales" VALUES(2,2,2);
    INSERT INTO "sales" VALUES(3,3,3);
    INSERT INTO "sales" VALUES(4,4,4);
    INSERT INTO "sales" VALUES(5,5,0);
    INSERT INTO "sales" VALUES(6,6,1);
    INSERT INTO "sales" VALUES(7,7,2);
    INSERT INTO "sales" VALUES(8,8,3);
    INSERT INTO "sales" VALUES(9,9,4);
    COMMIT;
    

    basically we have products ( iddb, px ) and sales of those products ( iddb, quantity, product_iddb )

    I load this data in power query by:

    A. creating an ODBC data source using SQLITE3 driver : testDSN

    B. in excel : data / new query , feeding this connection string Provider=MSDASQL.1;Persist Security Info=False;DSN=testDSN;

    Now in power query I add a computed column, say px10 = px * 10 to the product table. In the sales table, I can expand the product table into product.px, but not product.px10 . Shouldn't it be doable? ( in this simplified example I could expand first product.px and then create the px10 column in the sales table, but then any new table needinng px10 from product would require me to repeat the work... )

    Any inputs appreciated.

    Friday, October 23, 2015 12:49 PM

Answers

  • Sorry, I don't have SQLite, so I repeated your tables in Excel and then made query to sheet.

    May be it is better if you add calculated column directly in PowerQuery and then join tables like this:

    let
        PRODUCTS = Excel.CurrentWorkbook(){[Name="PRODUCTS"]}[Content],
        Add_px10 = Table.AddColumn(PRODUCTS, "px10", each [px]*10),
        SALES =Excel.CurrentWorkbook(){[Name="SALES"]}[Content],
        Comb = Table.NestedJoin(SALES,{"product_iddb"},Add_px10,{"iddb"},"NewColumn"),
        Extr = Table.ExpandTableColumn(Comb, "NewColumn", {"px10"}, {"px10"})
    in
        Extr
    here is the link to file: http://1drv.ms/1R0L1yI

    Friday, October 23, 2015 1:24 PM
  • I think I got what you mean... I think I missing something because my not so fluent English.

    You want to load some single table, like SALES, and it is already connected to PRODUCTS in your DB, and PRODUSTS has calculated field (in DB? or just in PQ?), but PQ does not expand this calculated field, right? and you would like to know, how to load ANY table, related to PRODUCTS, with possibility to excapnd calculated column, right?

    I'm sorry I do not know direct answer, may be more powerful guys can suggest - may be it depend on how this calculation looks for PQ. As far as I can suggest, when you expand this link to PRODUCTS, PQ looks to your connected table in DB, but (connected in DB) table does not have this calculated column. Calculated column exists only in PowerQuery, right?

    All I can suggest is make a separate query to PRODUCTS, save it as connection, and then, when you add a new query to other single table, join it with that first query of PRODUCTS. So you do not need to add query to PRODUCTS in each query to ither tables, you just join it with already calculated column

    Friday, October 23, 2015 2:59 PM

All replies

  • Sorry, I don't have SQLite, so I repeated your tables in Excel and then made query to sheet.

    May be it is better if you add calculated column directly in PowerQuery and then join tables like this:

    let
        PRODUCTS = Excel.CurrentWorkbook(){[Name="PRODUCTS"]}[Content],
        Add_px10 = Table.AddColumn(PRODUCTS, "px10", each [px]*10),
        SALES =Excel.CurrentWorkbook(){[Name="SALES"]}[Content],
        Comb = Table.NestedJoin(SALES,{"product_iddb"},Add_px10,{"iddb"},"NewColumn"),
        Extr = Table.ExpandTableColumn(Comb, "NewColumn", {"px10"}, {"px10"})
    in
        Extr
    here is the link to file: http://1drv.ms/1R0L1yI

    Friday, October 23, 2015 1:24 PM
  • Thanks a lot for your help Maxim.

    That solution only allows me to load the Extr table; what I would like to do is be able to use the Add_px10 in any table that is linked to PRODUCTS.

    Doing everything in one query like you suggest looks like the right approach; I would just need to be able to output multiple tables. I'll search if let ... in Xtable, Ytable, Ztable is possible, or perhaps nested let in ?

    Friday, October 23, 2015 2:01 PM
  • I think I got what you mean... I think I missing something because my not so fluent English.

    You want to load some single table, like SALES, and it is already connected to PRODUCTS in your DB, and PRODUSTS has calculated field (in DB? or just in PQ?), but PQ does not expand this calculated field, right? and you would like to know, how to load ANY table, related to PRODUCTS, with possibility to excapnd calculated column, right?

    I'm sorry I do not know direct answer, may be more powerful guys can suggest - may be it depend on how this calculation looks for PQ. As far as I can suggest, when you expand this link to PRODUCTS, PQ looks to your connected table in DB, but (connected in DB) table does not have this calculated column. Calculated column exists only in PowerQuery, right?

    All I can suggest is make a separate query to PRODUCTS, save it as connection, and then, when you add a new query to other single table, join it with that first query of PRODUCTS. So you do not need to add query to PRODUCTS in each query to ither tables, you just join it with already calculated column

    Friday, October 23, 2015 2:59 PM