Join ODBC connection with Excel Table

[Skovgaard]

Hi Experts,

Without luck I've explored the internet, so I hope one of you are able to help.

My below ODBC connection is huge, with more than 10 mill rows. I was hoping that I could join with an Excel table, in order to cut down loading time.
If I use the merge functionality in Power Query, it will load all 10 mill rows from the ODBC before the merge, and that is time consuming.

PS. I know the drilldown function with one parameter, but that is not suitable as I need more than one, therefore I was thinking join.


ODBC Connection:

SELECT FSLEDG.ESVONO
FROM M3.M3FDBPRD.FSLEDG FSLEDG

OR

Odbc.Query("dsn=M3_PROD", "SELECT FSLEDG.ESVONO#(lf)FROM M3.M3FDBPRD.FSLEDG FSLEDG")

Excel Table:

Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

/Skovgaard

 

[Skovgaard]

For your reference, I've just made a cross-post with same question on another forum, see link below.
If any luck, I'll write the solution in here.

If this is against the rules, mod. can delete this thread.

Limitation with multiple values in query load

/Skovgaard

 

[jdellasala]

An ODBC connection can be brought into the Data Model without Power Query from within Manage in the Power Pivot tab (also available in the Data tab).

That would bypass any PQ limitation, although I've imported over 10 million rows without any problem (except it taking FOREVER!).

 

[GraH]

Hi @Skovgaard,

I think in the cross post there is an interesting suggestion to use parameters.
Seems you are not too familiar with that concept, looking at your reaction over there.

The idea is to limit the ODBC Q1 with a list of products of your Excel Table Q2, right?

I understood the offered solution as followed:
- In Q2 you select the product column and turn it into a list. Make sure it has distinct values.
- Create a new parameter, give it a good name (selection?), set the type, use Query as Suggested values, select the query from the list drop down and use a dummy value in Current Value.
- Pass that Parameter into your ODBC, maybe like (this part I do not know, since I never use that type of connection.)

        Odbc.Query("dsn=M3_PROD", "SELECT FSLEDG.ESVONO#(lf)FROM M3.M3FDBPRD.FSLEDG FSLEDG WHERE M3.M3FDBPRD.PRODUCT = Selection")

I tried it in Excel as followed:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3		Product Selection					Product	Sales	Date
4		P01-dx451					P01-dx451	430,37	25/10/2022
5		P01-dl784					P01-dl784	374,62	16/11/2022
6		P02-DX654					P02-XD456	420,13	10/10/2022
7		P02-XD456					P03	427,77	5/10/2022
8							P03	228,04	14/10/2022
9							P03	415,16	25/10/2022
10							P03	206,02	3/11/2022
11							P04	261,72	26/10/2022
12							P01-dx451	466,36	5/10/2022
13							P01-dl784	152,11	7/10/2022
14							P02-XD456	214,13	3/10/2022
15							P05	471,74	19/11/2022
16							P05	413,57	15/10/2022
17							P06	454,75	10/11/2022
18
Sheet1

Both are loaded in PQ.

let
    Source = Excel.CurrentWorkbook(){[Name="Selections"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Selection", type text}}),
    #"Product Selection" = #"Changed Type"[Product Selection]
in
    #"Product Selection"

The parameter is defined as "Products"

"1" meta [IsParameterQuery=true, ExpressionIdentifier=Selections, Type="Text", IsParameterQueryRequired=true]

Here I do the filtering on the given selection.

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Sales", type number}, {"Date", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Product], Products))
in
    #"Filtered Rows"

That's the technique explained, you still need to figure it out to make it work with the ODBC connection, as onfortunately I cannot help you with that.

 

[Skovgaard]

Hi and thanks for your replies.

I found below homepage, which gave me a solution I understood.
It gave me what I needed, even though the loading process can be very long, if there is too many rows and columns in my table.

Dynamic SQL queries with Excel’s Power Query

Use an excel table to modify your SQL query If you regularly run queries to any database in your workplace, chances are you have encountered a user request like this: We can use Excel to query the database and get the information requested, but you will have to manually write the WHERE...

powergi.net

/Skovgaard