Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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.
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 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
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"
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
105 | |
104 | |
89 | |
65 |