Forum Discussion
Need to create 3 relationships between 2 tables?
- 6 years ago
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 AppendIf you have any questions, please kindly ask here and we will try to resolve it.
By the way, PBIX file as attached.
Best regards,
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.