Getting values from another table (Table 1) when Table 1 ID is between the value in Table 2

[Anonymous]

Getting values from another table (Table 1) when Table 1 ID is between the value in Table 2: Column 1 (From ID) AND Column 2 (To ID).

Lets say that Table one has two rows with there own unique ID's:.  The goal is to add: From ID, To ID and Cap to Table 1 if Table 1 is found between Table 2: From ID and To ID.  Question: Is this possible.  in Power BI Query Editor or some other method.

--------------------

row Table 1

Event No     ID

Big Event     3320204
Small Event  3320205

Rows Table 2

Row #    From ID     To ID         Cap

1             3320204    3320205   Yes

2             3320204    3320205    No

[Anonymous · Accepted solution]

So thanks again,

Table1 and Table2 are me just replicating your embedded values using the two DAX scripts you provided.  Good and thank you.

The other four tables are the data of the event values used for table one from the data tables: Events and Event Register.  the second set are just another set.  The Register Table always has one row as it really is the range of entry ID 's used in the posting of the initial Event Entries using the From ID AND the To ID.  and so the merge of the two (or JOIN) is showing when providing the list you have all the lines from the original posting and so a audit as it were.  Just having difficulty crafting the JOIN in SQL and so was attempting to do in Power BI. I just need to translate you query (ies) and make it work with mine other than must for the two embedded Event ID's you included in your scripts.

[Anonymous · Accepted solution]

Three days ago, I finally worked through this and did the question dataset in SQL as a SQL View as there are 70 event data organization databases with Event Log Entries and Event Registry Tables and it was a INNER JOIN using the Envents to the Registry Entries as the JOIN (ON) AND the Event Entry ID (Event Log)  BETWEEN the From Entry No (Registry Log) AND the To Entry No (also in Registry Log).  This then allowed me to join (add the columns from the Registry Log table to the Event Entry Table values.  because I had already with the Event Log Entries across all Event Organization in to on SQL View; I then Joined this as my main FROM source for the above INNER JOIN.    Not yet sure how to do this INNER JOIN in Power BI..  The Main JOIN between the Event Log Entries was the created nvarchar(40) for the Parent ID (with is the Event Company ID Code); and then the Event Entry ID AND the BETWEEN statement.

[jbwtp]

Hi @Anonymous,

This is definitely possible. This is one option:

let 
    Table1 = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspMV3AtS80rUdJRMjY2MjAyMFGK1YlWCs5NzMlBkzFVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Event No" = _t, ID = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event No", type text}, {"ID", Int64.Type}})
        in
            #"Changed Type",
    Table2 = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI2NjIwMjCBs0yBrMjUYqVYnWglIxzyfvlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row #" = _t, #"From ID" = _t, #"To ID" = _t, Cap = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row #", Int64.Type}, {"From ID", Int64.Type}, {"To ID", Int64.Type}, {"Cap", type text}})
        in
            #"Changed Type",
    BufferRightTable = Table.Buffer(Table2) // Buffereing to increase processing speed
,
    Merge = Table.AddColumn(Table1, "T2", (x)=>Table.SelectRows(BufferRightTable, each [From ID] <= x[ID] and [To ID] >= x[ID])),
    Expand = Table.ExpandTableColumn(Merge, "T2", {"From ID", "To ID", "Cap"}, {"From ID", "To ID", "Cap"})
in
    Expand

This is another:

let 
    Table1 = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspMV3AtS80rUdJRMjY2MjAyMFGK1YlWCs5NzMlBkzFVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Event No" = _t, ID = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event No", type text}, {"ID", Int64.Type}})
        in
            #"Changed Type",
    Table2 = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI2NjIwMjCBs0yBrMjUYqVYnWglIxzyfvlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row #" = _t, #"From ID" = _t, #"To ID" = _t, Cap = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row #", Int64.Type}, {"From ID", Int64.Type}, {"To ID", Int64.Type}, {"Cap", type text}})
        in
            #"Changed Type",
    BufferRightTable = Table.Buffer(Table2) // Buffereing to increase processing speed
,
    Merge = Table.AddColumn(Table1, "T2", (x)=>Table.SelectRows(BufferRightTable, each [From ID] <= x[ID] and [To ID] >= x[ID])),
    Expand = Table.ExpandTableColumn(Merge, "T2", {"From ID", "To ID", "Cap"}, {"From ID", "To ID", "Cap"})
in
    Expand

Depending on a dataset one may be quicker than another, try both and choose one that suits you most.

Cheers,

John

[Anonymous]

I was able to replicate your example, as renamed some columns in my two Tables.

You embedded the two ID:  3320204, & 3320205 Table one has these.  One event can have one, two, 100 or thousands.  Table two would have one row for each with a unique From ID and To ID.    So I am trying to get more than the results for the two form all the entries in Table 1 instead of just 3320204, & 3320205

[jbwtp]

Hi @Anonymous,

I've combined both source tables into one query just for demonstration purposes (to keep everything together), there is no practical benefit/neccessity for this.

Only the last 3 lines have practical value for you. Just adopt it for your queries/case.

Sorry, this is not clear for me from the sceenshot provided what you explain to me. Table 1 and Table 2 look same, is this expected behaviour? I thought they should be different (if tihs is still your source Table1 and Table2 tables)?

Thanks,

John

[Anonymous · Accepted solution]

So thanks again,

Table1 and Table2 are me just replicating your embedded values using the two DAX scripts you provided.  Good and thank you.

The other four tables are the data of the event values used for table one from the data tables: Events and Event Register.  the second set are just another set.  The Register Table always has one row as it really is the range of entry ID 's used in the posting of the initial Event Entries using the From ID AND the To ID.  and so the merge of the two (or JOIN) is showing when providing the list you have all the lines from the original posting and so a audit as it were.  Just having difficulty crafting the JOIN in SQL and so was attempting to do in Power BI. I just need to translate you query (ies) and make it work with mine other than must for the two embedded Event ID's you included in your scripts.