Forum Discussion
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
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"
8 Replies
- AlexisOlsonSuper 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.
- GrahamR99Resolver I
Hello AlexisOlson
How would I expand the user data table to have one row per entity in Power BI?
Regards
Graham
- AlexisOlsonSuper User
It depends on how your data is stored but this is an example of a similar transformation:
- YukiKImpactful Individual
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.