Create a relationship using a between two columns

[GrahamR99]

Hello 

I'm query Microsoft Business Central.  It has two tables.  The data table with EntityID, and a user data table which has "EntityID from" and "EntityID to".

If my EntityID is 6, how do I create a relationship to the user data table if "EntityID from" is 1 and "EntityID to" is 10. as 6 is between 1 and 10?

Is this possible?

The user data table has a user column that I want to show on the data table.

Regards

Graham

[YukiK]

I don't think this is possible in DAX itself. I'd do this in your ETL process where maybe adding a new key column that would connect to EntityID.

[AlexisOlson]

Not possible. Relationships have to match keys, not just compare them.

The solution would be to expand the user data table to have one row per entity.

[GrahamR99]

Hello AlexisOlson

How would I expand the user data table to have one row per entity in Power BI?

Regards

Graham

[v-kelly-msft]

Hi @GrahamR99 ,

Could you pls provide some dummy data with expected output for test?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[GrahamR99]

Hello @v-kelly-msft 

This is my data;

No.	Creation Date	User ID	Source	Journal Batch Name	From Entry No.	To Entry No.
1	01/10/2021	Graham Rock	IT	Batch001	1	15
2	02/10/2021	Simon Back	Sales	Batch002	16	26
3	03/10/2021	John Dowe	Sales	Batch003	27	30

I want it like this;

Entrie No.	Creation Date	User ID	Source	Journal Batch Name
1	01/10/2021	Graham Rock	IT	Batch001
2	01/10/2021	Graham Rock	IT	Batch001
3	01/10/2021	Graham Rock	IT	Batch001
4	01/10/2021	Graham Rock	IT	Batch001
5	01/10/2021	Graham Rock	IT	Batch001
6	01/10/2021	Graham Rock	IT	Batch001
7	01/10/2021	Graham Rock	IT	Batch001
8	01/10/2021	Graham Rock	IT	Batch001
9	01/10/2021	Graham Rock	IT	Batch001
10	01/10/2021	Graham Rock	IT	Batch001
11	01/10/2021	Graham Rock	IT	Batch001
12	01/10/2021	Graham Rock	IT	Batch001
13	01/10/2021	Graham Rock	IT	Batch001
14	01/10/2021	Graham Rock	IT	Batch001
15	01/10/2021	Graham Rock	IT	Batch001
16	02/10/2021	Simon Back	Sales	Batch002
17	02/10/2021	Simon Back	Sales	Batch002
18	02/10/2021	Simon Back	Sales	Batch002
19	02/10/2021	Simon Back	Sales	Batch002
20	02/10/2021	Simon Back	Sales	Batch002
21	02/10/2021	Simon Back	Sales	Batch002
22	02/10/2021	Simon Back	Sales	Batch002
23	02/10/2021	Simon Back	Sales	Batch002
24	02/10/2021	Simon Back	Sales	Batch002
25	02/10/2021	Simon Back	Sales	Batch002
26	02/10/2021	Simon Back	Sales	Batch002
27	03/10/2021	John Dowe	Sales	Batch003
28	03/10/2021	John Dowe	Sales	Batch003
29	03/10/2021	John Dowe	Sales	Batch003
30	03/10/2021	John Dowe	Sales	Batch003

How do I do this?

Regards

GrahamR99

[AlexisOlson · Accepted solution]

In the query editor, you can define a custom column as a list:

{[#"From Entry No."]..[#"To Entry No."]}

 Remove unneeded columns and expand the list column.

Here's a full M query you can past into your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1Dc00DcyMAJx3IsSMxJzFYLyk7OBPM8QIOGUWJKcYWAAkgVjU6VYnWglI5BOIySdwZm5+XkKTolgjcGJOanFCL0gxYZmQMLIDKzZGKTZGEmzV35GnoJLfnkqpl6QWiNzIGFsoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"No." = _t, #"Creation Date" = _t, #"User ID" = _t, Source = _t, #"Journal Batch Name" = _t, #"From Entry No." = _t, #"To Entry No." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"Creation Date", type date}, {"User ID", type text}, {"Source", type text}, {"Journal Batch Name", type text}, {"From Entry No.", Int64.Type}, {"To Entry No.", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Entrie No.", each {[#"From Entry No."]..[#"To Entry No."]}, type list),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"No.", "From Entry No.", "To Entry No."}),
    #"Expanded Entrie No." = Table.ExpandListColumn(#"Removed Columns", "Entrie No."),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Entrie No.",{"Entrie No.", "Creation Date", "User ID", "Source", "Journal Batch Name"})
in
    #"Reordered Columns"

[GrahamR99]

Thank you for your help all, it was great.

[AlexisOlson]

It depends on how your data is stored but this is an example of a similar transformation:

https://stackoverflow.com/questions/65030224