Need to create 3 relationships between 2 tables?

[Anmolgan]

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

A

B

C

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

[v-lid-msft]

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,

[parry2k]

@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]

@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]

@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.

[Anmolgan]

Hi @parry2k  am getting the attached output whenever i select all the fields required and unpviot them with the balance

[v-lid-msft · Accepted solution]

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,

[parry2k]

@Anmolgan can you share the image from navigation step