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

rasinc · 2024-05-26T00:32:10-0400

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.

AlienSx · 2024-05-26T03:05:18-0400

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

rasinc · 2024-05-26T15:44:09-0400

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.

Class	Start	End
1	100	199
2	200	299
3	300	399
4	400	699
5	700	899

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

Thank you

rasinc · 2024-05-26T23:14:41-0400

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!

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

rasinc

Board Regular

Excel Facts

AlienSx

New Member

rasinc

Board Regular

rasinc

Board Regular

Similar threads

Forum statistics

Share this page

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

rasinc

Board Regular

Excel Facts

AlienSx

New Member

rasinc

Board Regular

rasinc

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock