Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |