cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GrahamR99
Helper III
Helper III

Create a relationship using a between two columns

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

 

1 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"

View solution in original post

8 REPLIES 8
YukiK
Resolver IV
Resolver IV

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
Super User
Super User

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.

Hello AlexisOlson

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

 

Regards

 

Graham

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!

Hello @v-kelly-msft 

This is my data;

No.Creation DateUser IDSourceJournal Batch NameFrom Entry No.To Entry No.
101/10/2021Graham RockITBatch001115
202/10/2021Simon BackSalesBatch0021626
303/10/2021John DoweSalesBatch0032730

 

I want it like this;

Entrie No.Creation DateUser IDSourceJournal Batch Name
101/10/2021Graham RockITBatch001
201/10/2021Graham RockITBatch001
301/10/2021Graham RockITBatch001
401/10/2021Graham RockITBatch001
501/10/2021Graham RockITBatch001
601/10/2021Graham RockITBatch001
701/10/2021Graham RockITBatch001
801/10/2021Graham RockITBatch001
901/10/2021Graham RockITBatch001
1001/10/2021Graham RockITBatch001
1101/10/2021Graham RockITBatch001
1201/10/2021Graham RockITBatch001
1301/10/2021Graham RockITBatch001
1401/10/2021Graham RockITBatch001
1501/10/2021Graham RockITBatch001
1602/10/2021Simon BackSalesBatch002
1702/10/2021Simon BackSalesBatch002
1802/10/2021Simon BackSalesBatch002
1902/10/2021Simon BackSalesBatch002
2002/10/2021Simon BackSalesBatch002
2102/10/2021Simon BackSalesBatch002
2202/10/2021Simon BackSalesBatch002
2302/10/2021Simon BackSalesBatch002
2402/10/2021Simon BackSalesBatch002
2502/10/2021Simon BackSalesBatch002
2602/10/2021Simon BackSalesBatch002
2703/10/2021John DoweSalesBatch003
2803/10/2021John DoweSalesBatch003
2903/10/2021John DoweSalesBatch003
3003/10/2021John DoweSalesBatch003

 

How do I do this?

 

Regards

 

GrahamR99

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"

View solution in original post

Thank you for your help all, it was great.

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

https://stackoverflow.com/questions/65030224

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.