Creating a report matrix from three tables

Hi Anonymous , 

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.

 

Hello Anonymous 

 

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