import data into model based on value in existing table

[abeckerpharm]

I'm trying to import data into a table only if it matches the primary key from an existing table. Cannot get import to work. Trying to do it with a table join. Don't know if thats the best way with M code. Please take a look:

let

Source = Odbc.DataSource("dsn=serverOnSite", [HierarchicalNavigation=true]),
dharma_Schema = Source{[Name="dharma",Kind="Schema"]}[Data],
Joined = Table.Join(#"POS Transactions_Table", "Record Key", #"POS Transactions-Item Info_Table", "Invoice Number"),
#"POS Transactions-Item Info_Table" = dharma_Schema{[Name="POS Transactions-Item Info",Kind="Table"]}[Data],
#"POS Transactions_Table" = dharma_Schema{[Name="POS Transactions",Kind="Table"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"POS Transactions-Item Info_Table", each (Joined = true))

in
#"Filtered Rows"

[abeckerpharm]

I'm trying to avoid importing the whole table. It's millions of records and I only need the rows that match the primary key from the other table. I would just to it with sql and park it in a table to import into power bi, but this DB is an odd one and won't connect with anything but ODBC.

[v-jingzhang]

Hi @abeckerpharm 

Power Query don't import all data when you transform data in Power Query Editor. It only loads data preview of 1000 rows. You can connect to two tables separately in two queries first. Then in the primary key table, try Inner join or Full outer join to bring matched data from the big table. When expanding, only select columns you want. As the table is very big, this step may take a while. 

When this table is ready, in the left Queries pane, right click on the big table query and uncheck Enable load option. The big table will not be imported into the model. Then click Apply to import the transformed data into model.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@abeckerpharm , After you import the table, do an inner join merge with another table. That should help