Need to create 3 relationships between 2 tables?

Hi I need to get some amounts details correctly which are mapped with another values think of it as below:

type bal ty bal ty1 Bal

A 40 A 90 A 300

B 20 B 60 B 100

C 30 C 70 C 110

and other table that I have is

Type

Now when I merge the type for table1 to type with table 2 other balance dont get mapped correctly, now what if I try to break the table among 3 distinct types table so that I can break the table and then create 3 relationships with the table2, will I get my calcualtions?

Is there any best way to do this so that all my types and balance align correctly with the artypes

Hi Anmolgan ,

We can add a new step in Power Query Editor to meet your requirement:

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIxABIghiWMYWxgoBSrE63kBGQbgQRBDDMYwxAq6wxWCSRADHMYw9AQKBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, bal = _t, ty = _t, ba = _t, ty1 = _t, Bal = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"bal", Int64.Type}, {"ty", type text}, {"ba", Int64.Type}, {"ty1", type text}, {"Bal", Int64.Type}}),
    #"NameOfYourLastStep" = Table.RenameColumns(#"Changed Type",{{"Bal", "Bal.1"}}),
    Append = Table.Combine({
        Table.SelectColumns(#"NameOfYourLastStep",{"Type","bal"}),
        Table.RenameColumns(
            Table.SelectColumns(#"NameOfYourLastStep",{"ty","ba"})
            ,{{"ty","Type"}
            ,{"ba","bal"}
            }),
        Table.RenameColumns(
            Table.SelectColumns(#"NameOfYourLastStep",{"ty1","Bal.1"})
            ,{{"ty1","Type"}
            ,{"Bal.1","bal"}
            })
    })
in
    Append

If you have any questions, please kindly ask here and we will try to resolve it.

By the way, PBIX file as attached.

Best regards,

Need-to-create-3-relationships-between-2-tables.pbix39 KB

7 Replies

parry2k
Super User
6 years ago
Anmolgan you should unpivot your type1 table (table with balance) and that will do it. To do unpivot, go to query editor, remove ty and ty1 column, select type column, right click, inpivot other column, you will get two columns in your table "Attribute" and Value, apply the changes.

Set the relationship between type 1 and type 2, on matrix visual, use Type from Type 2 table, attribute on columns, and value on value section and everything should flow.

Would appreciate Kudos 🙂 if my solution helped.
- Anmolgan
  Post Prodigy
  6 years ago
  parry2k I should unpivot all the types in table and then build the relationship to the receipt table? will that align my calculations correctly?
  - parry2k
    Super User
    6 years ago
    Anmolgan based on what you provided, yes it should. Test it and let me know for any issue. Do provide further details if doesn't work.
v-lid-msft
Community Support
6 years ago
Hi Anmolgan ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Forum Discussion

Need to create 3 relationships between 2 tables?