Forum Discussion

GrahamR99's avatar
GrahamR99
Resolver I
4 years ago
Solved

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...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    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's avatar
GrahamR99
Resolver I
4 years ago

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

AlexisOlson's avatar
AlexisOlson
Super User
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's avatar
    GrahamR99
    Resolver I
    4 years ago

    Thank you for your help all, it was great.