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

[rasinc]

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

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]

transform ID column into type text before doing this

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]

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

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]

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!

 

[AlienSx]

Hello, rasinc
{X..Y} syntax creates a list where X and Y should be either numbers or single characters. That's why code throws an error.
Glad to hear that you solved your problem. There is another option you could try (I assume that Start, End and ID fields are all numbers):

let
    table1 = #table(type table [ID = number, data1 = any], {{25, "aaa"}, {88, "bbb"}}),
    table2 = #table(type table [Start = number, End = number, Class = any], {{5, 30, "ccc"}, {50, 150, "ddd"}}),
    // unpivot Start and End, with "ID" as values column name
    unpivot_table2 = Table.UnpivotOtherColumns(table2, {"Class"}, "SE", "ID"),
    append_table1 = unpivot_table2 & table1,
    sort_by_ID_SE = Table.Sort(append_table1,{{"ID", Order.Ascending}, {"SE", Order.Descending}}),
    fill_down_Class = Table.FillDown(sort_by_ID_SE, {"Class"}),
    // and remove table2 rows by filtering
    filter_SE = Table.SelectRows(fill_down_Class, each ([SE] = null))
in
    filter_SE

remove columns you don't want in the end. You don't need to get first 3 digits since we are not generating a list of numbers. Sorting and maybe unpivot steps may take some resources. But give it a try.

 

[rasinc]

Thank you for the additional information. I will take a look at this alternative but it will take a couple of days before I can get back to it. I had tried the unpivot but didn't know where to go from there. I never thought about the append query and fill options, though I have used them both in other projects. This looks interesting and may be quicker. The total records in table 1 may be as many as 500-600 rows in most cases so I don't think it will hit resources too hard.