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

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.

GrahamR99's avatar
GrahamR99
Resolver I
4 years ago

Hello AlexisOlson

How would I expand the user data table to have one row per entity in Power BI?

 

Regards

 

Graham

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    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!

    • 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"