Creating a report matrix from three tables

[alexdi]

This is a little tricky. I'm trying to take outputs from three tables and generate a matrix of the relationship.

I haven't been able to figure out what combination of unpivots/transforms creates the result. Any thoughts?

Sample here with connections:

https://gofile.io/?c=6ZgkTP

[dax · Accepted solution]

Hi @alexdi , 

You could try to use below M code to achieve this goal.

Merge 1

let
    Source = Table.NestedJoin(Group, {"group"}, GroupItem, {"group"}, "GroupItem", JoinKind.LeftOuter),
    #"Expanded GroupItem" = Table.ExpandTableColumn(Source, "GroupItem", {"item"}, {"item"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded GroupItem", {"item"}, Item, {"item"}, "Item.1", JoinKind.FullOuter),
    #"Expanded Item.1" = Table.ExpandTableColumn(#"Merged Queries", "Item.1", {"id", "item"}, {"id", "item.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Item.1",null,0,Replacer.ReplaceValue,{"id", "item", "group", "item.2"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Replaced Value", {{"group", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Replaced Value", {{"group", type text}}, "en-US")[group]), "group", "item"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"0"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([id] <> 0)),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [groupname1], each if [groupname1]<>null then "Y" else [groupname1],Replacer.ReplaceValue,{"groupname1"}),
    Custom1 = Table.ReplaceValue(#"Replaced Value1",each [groupname2], each if [groupname2]<>null then "Y" else [groupname2],Replacer.ReplaceValue,{"groupname2"})
in
    Custom1

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801 · Accepted solution]

Hello @alexdi 

try out this solution. It follows an dynamic approach, and should always work.

Create a new blank query in your Excel and paste this code

let
    Quelle = Table.Combine({GroupItem,Group}),
    Groupint= Table.Group(Quelle, {"item"}, {{"AllRows", each _, type table}}),
    Transform = Table.TransformColumns
    (
        Groupint,
        {
            {
                "AllRows",
                (tableint)=>
                    Table.ReplaceValue
                    (
                        Table.PromoteHeaders
                        (
                            Table.Transpose(tableint)
                        ),
                        "X",
                        "X",
                        (x,y,z)=> y,
                        Table.ColumnNames(Table.PromoteHeaders
                        (
                            Table.Transpose(tableint)
                        ))
                    )                 
            }
        }
    ),
    Expand = Table.ExpandTableColumn(Transform, "AllRows", List.Distinct(Table.ColumnNames(Table.Combine(Transform[AllRows])))),
    Join = Table.NestedJoin(Item,"item",Expand,"item","new"),
    ExpandFinal = Table.ExpandTableColumn(Join, "new", List.Difference(List.Distinct(Table.ColumnNames(Table.Combine(Join [new]))), {"item"}))
in
    ExpandFinal

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[alexdi]

You guys are wizards, those are both terrific solutions, thank you! Now to reverse-engineer how you did it... 🙂

[Jimmy801]

Hello @alexdi 

thanks for the feedback

It's much appreciated

Jimmy

[Greg_Deckler]

Seems like you want a bridge table between Item and GroupItem (I do not like many to many). Then you should be able to create a matrix with your ID, Item and your Group in Columns and then probably a simple measure that does a COUNT and if it is more than 0 then Y, otherwise BLANK.

[alexdi]

Apologies, I don't quite follow. Joining Item and GroupItem gets to this:

I can also create this:

But I'm not sure how to fill it in. 

[dax · Accepted solution]

Hi @alexdi , 

You could try to use below M code to achieve this goal.

Merge 1

let
    Source = Table.NestedJoin(Group, {"group"}, GroupItem, {"group"}, "GroupItem", JoinKind.LeftOuter),
    #"Expanded GroupItem" = Table.ExpandTableColumn(Source, "GroupItem", {"item"}, {"item"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded GroupItem", {"item"}, Item, {"item"}, "Item.1", JoinKind.FullOuter),
    #"Expanded Item.1" = Table.ExpandTableColumn(#"Merged Queries", "Item.1", {"id", "item"}, {"id", "item.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Item.1",null,0,Replacer.ReplaceValue,{"id", "item", "group", "item.2"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Replaced Value", {{"group", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Replaced Value", {{"group", type text}}, "en-US")[group]), "group", "item"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"0"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([id] <> 0)),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [groupname1], each if [groupname1]<>null then "Y" else [groupname1],Replacer.ReplaceValue,{"groupname1"}),
    Custom1 = Table.ReplaceValue(#"Replaced Value1",each [groupname2], each if [groupname2]<>null then "Y" else [groupname2],Replacer.ReplaceValue,{"groupname2"})
in
    Custom1

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.