Multiple (2) Matrix Table Links with 1 main table...achiveable?

[AliRade]

Hi Community,

I'm wondering what is the best way to link three tables together (in which two are like matrix tables). It's not a simple link (at least for me) and seeking the most efficient way of doing this (instead of having one giant table).

The first table is the main table. It's linked to the second table (a matix table) by "Type" (Coloured in Yellow).

The first table is also linked to the third table (another matrix table) by "Group) (Coloured in Blue).

The second table and third table are not linked as yet but can be by "RoleIDX" (Coloured in Orange).

What I'm trying to get is for each ID row in first table, what is the the "MainRole_ID" (Coloured in Green - in Outcome table). I can manually and logically think this out but not sure of best ways to do it in PBI.

Logic:

Use "Type" (Yellow) from main table and identify which "RoleIDX" (Orange) has a "RA" against it (in second table). Once we know which "RoleIDX" has the "RA", go to third table (and using "Group" [Blue]), get the code from Group/RoleIDX.

For example, using the first row (ID = 12141) from main table, Type is "1" and the "RA" is under "RoleID6". ID 12141 is from "Group1" so the Code is Group1/RoleID6 = "LR3". This is the main goal (GOAL 1) I'm trying to get.....but unsure how to go about it.

The second goal is, IF the Type from table 2 is "Multi" (i.e. it has multiple RoleIDX with "RA"), then it needs to look into the "TypeLVL2" from main table 1 and advancing on from GOAL 1, give the code by also taking into account "TypeLVL2". For example, the third last row ID = 14201 has Type "15" which has "RA" under multiple RoleIDX in table 2. This Row ID has TypeLVL2 = "EA". So the final outcome code would be Type "15" / TypeLVL2" EA" / Group "Group5" = "LP1".

Table 1 - Main Table

Table1

Table 2 - (Matrix 1)

Matrix1

Table 3 - (Matrix 2)

Matrix2

Outcome

Without blowing out the 2 matrix tables into 1 giant table which will take into account all possible combinations, whats the best way to about this?

[dufoq3 · Accepted solution]

Hi @AliRade, check this:

Result

let
    T1Main = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdA9C4MwEAbgv1IyO+TOnDWjinRqKXQUhw4dS6XQ/98786FJoB0iEh7eu7zTpADBgKqUnHPHn8v9+TjIz+n9+iyg5oqRwfo/OgIhXxk+Y0R9hqxBAZigISB0qGXm0bKN6zOFBCIoXSpm1U6RRuuzNtV3WVZjQfNVw2e4FbsHhCgN2J+ICGWjNW43MK5Fvgfb1sIoDcuZQQ2BjWXzFPrSa5iUdi2rN165NwIkI/cT5y8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, TypeLVL2 = _t, Name = _t, Group = _t]),
    T2Matrix1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfKE4rzSnBwMKsgRlwwqFZyZl56TqhSrE61kRLYhhEw2JqCUoD0wryKZaYIpS4SjPNFsQzLQlHhTCDobyVgzYv1HWpCakxF6mLYgGWhBQVRjCRYkky0xXUJswvLE6lRDA5gR2AgizfMtzSnJhBhnqIQtbAgaBlaAw4VGWO0kMRWhpXhDQtmIXHNNKDUQZ6IyNMUXU8jpEovh0BiKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, RoleID1 = _t, RolelD2 = _t, RoleID3 = _t, RoleID4 = _t, RolelD5 = _t, RoleID6 = _t, RolelD7 = _t, RolelD8 = _t, RolelD9 = _t, RolelD10 = _t, RolelD11 = _t, #"Single/Multi" = _t]),
    T3Matrix2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVC7DoMwEPuVKjNDefWxk4tKUxEFxIIYu0WlqsT/l7ukF6CLMTaxHYZBqM80v0Ui7OSetyoNzFUZazmzgt2StRNrZ2YXZldm6THSVIxJaMbCtvOIlT0gPiyipjna5uRiOdAsBW7Bu0Rdm8MqDV2j8RRI5ECZjUHlNTsXH7UsdqgprzFZjPMFcVxN9SHBS9BGQ8s/G6s5rtjdNXzZrBp83HaqpSPWL1imcl7JeWFk7++8iqO/pgi3odDlv5dx/AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TypeLVL2 = _t, MA = _t, EA = _t, PA = _t, CS = _t, DF = _t, WS = _t, KF = _t, EW = _t, FA = _t, JU = _t, RY = _t]),
    T2_Unpivoted = Table.UnpivotOtherColumns(T2Matrix1, {"Type"}, "RoleID", "Value"),
    T3_DemoteHeaders = Table.DemoteHeaders(T3Matrix2),
    T3_TransposedTable = Table.Transpose(T3_DemoteHeaders),
    T3_PromotedHeaders = Table.PromoteHeaders(T3_TransposedTable, [PromoteAllScalars=true]),
    T3_RenamedColumns = Table.RenameColumns(T3_PromotedHeaders,{{"Group", "RoleID"}}),
    T3_Unpivoted = Table.UnpivotOtherColumns(T3_RenamedColumns, {"TypeLVL2", "RoleID"}, "Group", "Value"),
    T2_SelfMerge_T3 = Table.NestedJoin(T2_Unpivoted, {"RoleID"}, T3_Unpivoted, {"RoleID"}, "T3_Unpivoted", JoinKind.LeftOuter),
    T2_ExpandedT3_Unpivoted = Table.ExpandTableColumn(T2_SelfMerge_T3, "T3_Unpivoted", {"Group", "TypeLVL2", "Value"}, {"T2Group", "T2TypeLVL2", "T2Value"}),
    T2_FilteredRA = Table.SelectRows(T2_ExpandedT3_Unpivoted, each ([Value] = "RA")),
    StepBackToT1 = T1Main,
    SelfMerge_T1_T2 = Table.NestedJoin(StepBackToT1, {"Type"}, T2Matrix1, {"Type"}, "T2", JoinKind.LeftOuter),
    AddedIndex = Table.AddIndexColumn(SelfMerge_T1_T2, "Index", 0, 1, Int64.Type),
    ExpandedT2 = Table.ExpandTableColumn(AddedIndex, "T2", {"Single/Multi"}, {"Single/Multi"}),
    SelfMergeT2_Single = Table.NestedJoin(ExpandedT2, {"Group", "Type"}, T2_FilteredRA, {"T2Group", "Type"}, "T2_Single", JoinKind.LeftOuter),
    SelfMergeT2_Multi = Table.NestedJoin(SelfMergeT2_Single, {"Group", "Type", "TypeLVL2"}, T2_FilteredRA, {"T2Group", "Type", "T2TypeLVL2"}, "T2_Multi", JoinKind.LeftOuter),
    ExpandedT2_Multi = Table.ExpandTableColumn(SelfMergeT2_Multi, "T2_Multi", {"T2Value"}, {"T2Value"}),
    SortedRows = Table.Sort(ExpandedT2_Multi,{{"Index", Order.Ascending}}),
    Ad_MainRoleID = Table.AddColumn(SortedRows, "MainRole_ID", each if [T2Value] <> null then [T2Value] else Table.First([T2_Single])[T2Value], type text),
    RemovedOtherColumns = Table.SelectColumns(Ad_MainRoleID,{"ID", "Group", "Type", "TypeLVL2", "Single/Multi", "MainRole_ID"})
in
    RemovedOtherColumns

[lbendlin]

Power Query does not like Matrix tables. Unpivot them to bring them into a usable format.