Joining Tables with Different number of rows and columns

[Anonymous]

Hi! I am looking to implement a solution that will create a joined table at created from two tables that have different numbers of rows / columns. To make the example and ideal outcome simpler, I have included example tables below. 

Table 1 consists of Categories (that get repeated in each row) and subcategories that roll up / align to the categories.  

Table 2 consists of the Subcategories from Table 1, but with "Yes / No / N/A" fields to account for certain criteria within the dataset. The criteria ID is just a binary field in Power BI that uses the value to define it numerically.

The ideal table will have everything rolled up to the "Category" level, so the number of rows will be equal to the number of Categories. Then the columns will expand to the number of Subcategories within the dataset, then creating a SUM of the binary criteria ID's that will be used in the report. 

I am seeking a solution that will create this functionality via Power Query or the Data view. Please see examples below as that may clarify the ask!

Thank you!

Table 1
Category	Subcategory
AA.BB-1	Z1
AA.BB-1	X2
AA.BB-1	V3
CC.DD-2	Q1
CC.DD-2	W2
EE.FF-1	P1
EE.FF-1	R3
EE.FF-1	K4
EE.FF-1	L5
EE.FF-1	M8
EE.FF-1	G2

Table 2
Subcategory	Criteria 1	Criteria 2	Criteria 3	Criteria 1 ID	Criteria 2 ID	Criteria 3 ID
Z1	Yes	N/A	N/A	1	0	0
X2	No	No	Yes	0	0	1
V3	No	No	N/A	0	0	0
Q1	Yes	No	No	1	0	0
W2	Yes	N/A	N/A	1	0	0
P1	Yes	Yes	Yes	1	1	1
R3	No	Yes	Yes	0	1	1
K4	No	No	Yes	0	0	1
L5	Yes	Yes	No	1	1	0
M8	No	N/A	N/A	0	0	0
G2	Yes	No	No	1	0	0

Ideal Outcome
Category	Subcategory 1	Subcategory 2	Subcategory 3	Subcategory 4	Subcategory 5	Subcategory 6	Sum(Criteria ID)
AA.BB-1	Z1	X2	V3	N/A	N/A	N/A	2
CC.DD-2	Q1	W2	N/A	N/A	N/A	N/A	2
EE.FF-1	P1	R3	K4	L5	M8	G2	9

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can achieve this with Power Query.

M codes:

let
    Source = Table.NestedJoin(#"Table 1", {"Subcategory"}, #"Table 2", {"Subcategory"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(Source, "Table 2", {"Criteria 1 ID", "Criteria 2 ID", "Criteria 3 ID"}, {"Criteria 1 ID", "Criteria 2 ID", "Criteria 3 ID"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table 2", "Sum(Criteria ID)", each [Criteria 1 ID]+[Criteria 2 ID]+[Criteria 3 ID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Criteria 1 ID", "Criteria 2 ID", "Criteria 3 ID"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Category"}, {{"GroupTable", each _, type table [Category=nullable text, Subcategory=nullable text, #"Sum(Criteria ID)"=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "SubcategoryList", each [GroupTable][Subcategory]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"SubcategoryList", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "SubcategoryList", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"SubcategoryList.1", "SubcategoryList.2", "SubcategoryList.3", "SubcategoryList.4", "SubcategoryList.5", "SubcategoryList.6"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SubcategoryList.1", type text}, {"SubcategoryList.2", type text}, {"SubcategoryList.3", type text}, {"SubcategoryList.4", type text}, {"SubcategoryList.5", type text}, {"SubcategoryList.6", type text}}),
    #"Aggregated GroupTable" = Table.AggregateTableColumn(#"Changed Type", "GroupTable", {{"Sum(Criteria ID)", List.Sum, "Sum of Sum(Criteria ID)"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Aggregated GroupTable",null,"N/A",Replacer.ReplaceValue,{"SubcategoryList.1", "SubcategoryList.2", "SubcategoryList.3", "SubcategoryList.4", "SubcategoryList.5", "SubcategoryList.6"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Category", "SubcategoryList.1", "SubcategoryList.2", "SubcategoryList.3", "SubcategoryList.4", "SubcategoryList.5", "SubcategoryList.6", "Sum of Sum(Criteria ID)"})
in
    #"Reordered Columns"

Download the attached pbix to see all steps.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Fowmy]

@Anonymous 

In Power Query UnPivot table 2 using the technique shown here and merge table 1 to table 2 using subcategory then expand the Category. You Pivot the Sub Category and get the result or use a matrix visual in Power BI.

You can UnPivot and create a relationship then use a matrix to visualize.