Power Query: Merge Query Where Table 1 Has One Field Matching Two Fields in Table 2

rasinc

Board Regular
Joined
Jun 28, 2011
Messages
122
Right now I am working in Power BI Desktop but I can see a scenario where I may need to use these tables in Power Query in Excel.

The data is in a MySQL database that is linked and pulls data properly.

I have a couple of cases where I have to pull data from multiple tables where the ON statement in MySQL would look something like

SQL:
ON (`table1`.`ID` >= `table2`.`Start`) AND (`table1`.`ID` <= `table2`.`End`)

I need to ultimately transform the data to pull the table2.ID field and add it to table 1 for further use.

I've tried to unpivot Start and End but only some records match. I considered trying to temporarily add enough records to match every number between start and end for every record, but I don't know how to do that either and I must not be searching the correct terms or it is not possible.

Table 2 has less than 10 records. A record for start Start could be something like 24 and End 177 (all numeric) or 355 and 827 respectively.

Is there a way to accomplish this? Any help is appreciated. TIA rasinc.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
transform ID column into type text before doing this
Power Query:
let
    table1 = #table(type table [ID = text, data1 = any], {{"25", "aaa"}, {"88", "bbb"}}),
    table2 = #table({"Start", "End", "data2"}, {{5, 30, "ccc"}, {50, 150, "ddd"}}),
    comb = Table.CombineColumns(table2, {"Start", "End"}, (x) => List.Transform({x{0}..x{1}}, Text.From), "ids"),
    xp = Table.ExpandListColumn(comb, "ids"),
    rec = Record.FromList(xp[data2], xp[ids]),
    join = Table.AddColumn(table1, "data2", (x) => Record.FieldOrDefault(rec, x[ID]))
in
    join
 
Upvote 0
Solution
Thank you for the quick reply AlienSx. However, I have to admit you scared the heck out of me with that code. I am what I call a beginner with Power BI and Power Query but have some programming experience. While it looked confusing, I decided to piece apart your code and see what it was doing. My goal is really to determine if what I normally do with reporting in VB.Net, Crystal Reports or Excel VBA, can be replaced more easily with Power Query/Power BI.

After some trial and error I got it to work on my MySQL data tables. But I apologise for simplifying the concepts. When I ran it on my actual data, I lost track of how long it took to do everything (including the above parts and further transformations after this stage with other related data tables). I'll have to tear it apart to figure out where the problem really lies.

The simplification was that I used smaller numbers ranges than are real in my example above. The real ranges includes 8 digit numbers for Start and End, so at one point, when it ran, I ran out of memory working on just the table2 part.

When I ran out of memory I started reviewing the Start and End numbers and found I could do the same thing with a comparison of the first three digits.

This is the current transformation of table2 and the error I am getting

Power Query:
let
    Source = Odbc.DataSource("dsn=myDSN", [HierarchicalNavigation=true]),
    MyDatabase = Source{[Name="MyDatabase",Kind="Database"]}[Data],
    table2 = MyDatabase_Database{[Name="table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(table2,{{"Class", Int64.Type}}),  /*  this is the column I want in table1 */
    #"Extracted First Characters" = Table.TransformColumns(#"Changed Type", {{"Start", each Text.Start(Text.From(_, "en-CA"), 3), type text}, {"End", each Text.Start(Text.From(_, "en-CA"), 3), type text}}),
    comb = Table.CombineColumns(#"Extracted First Characters", {"Start", "End"}, (x) => List.Transform({x{0}..x{1}}, Text.From), "ids"),   
 
/*Error Here on comb line:
Expression.Error: The value isn't a single-character string.
Details:
    Value=300  - Note each row has different error value - only five original rows right now */

    xp = Table.ExpandListColumn(comb, "ids"),
    #"Changed Type1" = Table.TransformColumnTypes(xp,{{"ids", Int64.Type}})
in
    #"Changed Type1"

The code modifications I wrote based on your code is now working except for the time it takes. This is with the 8 digit numbers. I am testing to see if the above transformation down to 3 digits will make the downstream transformations faster. Ideally this should reduce the number of rows needed when the list of ids are created. I see no reason why the above shouldn't work but it returns the above error every time.

The current database I am working on has the following data (when converted from 8 to 3 digits) but while usually by hundreds, can be anything from 100 up to a max of 999 depending on the database I am working on.
ClassStartEnd
1100199
2200299
3300399
4400699
5700899

Would you know why might be wrong with the above code?

Thank you
 
Upvote 0
AlienSx

Please ignore my last message. I have now sorted out what was wrong with my modifications of your code. Extracting the first x characters from a number field, turns the field into text and I missed it completely. To do the comb and xp lines in your code, I needed the change the column format back to Number. I have fixed the processing speed now with other modifications not related to this thread. I still have some testing to do but my initial checking looks like your code helped a lot and got the results I was looking for.

Thank you very much!
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top