Forum Discussion
Create a relationship using a between two columns
- 4 years ago
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"
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.
- GrahamR994 years ago
Resolver I
Hello AlexisOlson
How would I expand the user data table to have one row per entity in Power BI?
Regards
Graham
- AlexisOlson4 years ago
Super User
It depends on how your data is stored but this is an example of a similar transformation:
- v-kelly-msft4 years ago
Community Support
Hi GrahamR99 ,
Could you pls provide some dummy data with expected output for test?
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
- GrahamR994 years ago
Resolver I
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