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