Combine Rows and show in a Table

Question

Hi,I have the below two tables in my source.&nbsp;Table1:&nbsp;EngagementSLA &amp; Obligation RAGSLA &amp; Obligation RemarksStability &amp; Availability RAGStability &amp; Availability RemarksSecurity &amp; Vulnerability RAGSecurity &amp; Vulnerability RemarksCustomer SentimentTAAround 5 SLA failedGAround 5 Major issue reportedA&nbsp;NDGAround 5 SLA failedRNo Major incdient reportedGVulnerability Remediation plan In placeHLRAround 5 SLA failedANo Major incdient reportedRVulnerability Remediation plan In placeU&nbsp;Table2:&nbsp;EngagementMS / PSRAGTopicOwnerImplicationsAction RequiredTimingSequenceDashboard VisibilityTMSATopic1Around 5 SLA failedGAround 5 Major issue reportedA1YDMSGTopic3Around 5 SLA failedGNo Major incdient reportedG2YTMSATopic2Around 10 SLA failedRAround 50 Major issue reportedR1Y&nbsp;I made a connection between the tables based on Engagement column.&nbsp;I would like to create a table visual with these columns.&nbsp;My current output:My Expected Output:&nbsp;I would like to combine the rows based on Engagement, which is possible on Matrix view but I need to apply RAG coloring as per my current output.&nbsp;&nbsp;Is there any ways to combine the rows (not all rows) of Table 2 based on the Engagement column so that I can use them.&nbsp;&nbsp;Kindly help me to get the same.&nbsp;@amitchandak&nbsp;@Greg_Deckler&nbsp;@lbendlin&nbsp;@DesktopOwl&nbsp;@Goodlytics4U&nbsp;@HelpMe&nbsp;@Ahmedx&nbsp;@v-huijie-msft&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Accepted Answer

Thanks for the reply from amitchandak&nbsp;, please allow me to provide another insight:
Hi,&nbsp;gssarathkumar&nbsp;
We currently have two solutions, each with its own drawbacks. You can choose based on your needs:
&nbsp;
1.Firstly, you can merge the two tables and create an index, keeping only the first one:
&nbsp;
First, create an index column in Power Query:
let
    Source = Table.NestedJoin(Table, {"Engagement"}, #"Table (2)", {"Engagement"}, "Table (2)", JoinKind.RightOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Topic", "Owner", "Implications", "Action Required", "Timing"}, {"Table (2).Topic", "Table (2).Owner", "Table (2).Implications", "Table (2).Action Required", "Table (2).Timing"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table (2)", {"Engagement"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"SLA &amp; Obligation Remarks", "Stability &amp; Availability Remarks", "Security &amp; Vulnerability Remarks", "Customer Sentiment", "Table (2).Topic", "Table (2).Owner", "Table (2).Implications", "Table (2).Action Required", "Table (2).Timing", "Index"}, {"Count.SLA &amp; Obligation Remarks", "Count.Stability &amp; Availability Remarks", "Count.Security &amp; Vulnerability Remarks", "Count.Customer Sentiment", "Count.Table (2).Topic", "Count.Table (2).Owner", "Count.Table (2).Implications", "Count.Table (2).Action Required", "Count.Table (2).Timing", "Count.Index"})
in
    #"Expanded Count"
Next, use the following calculated column:
engagement1 = 
VAR FIRST=CALCULATE(MIN('Merge1'[Count.Index]),ALLEXCEPT('Merge1','Merge1'[Engagement]))
RETURN IF('Merge1'[Count.Index]=FIRST,'Merge1'[Engagement],BLANK())
There is a link to a similar issue:
https://community.fabric.microsoft.com/t5/Desktop/Group-by-ID-Skip-Aggregation/td-p/3976285
Here's my final result, which I hope meets your requirements.

2.Secondly, you mentioned using the matrix view:
&nbsp;
You only need to modify the matrix settings as shown in the image below:

ere's my final result, which I hope meets your requirements.

&nbsp;
Please find the attached pbix relevant to the case.
&nbsp;Of course, if you have any new ideas, you are welcome to contact us.&nbsp;
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Combine Rows and show in a Table

2 Replies

Related content

Tables combine

Combine 2 rows into 1

Create rows from 2 tables

Combining 2 tables without matching rows

Combine values from multiple rows

Engagement	SLA & Obligation RAG	SLA & Obligation Remarks	Stability & Availability RAG	Stability & Availability Remarks	Security & Vulnerability RAG	Security & Vulnerability Remarks	Customer Sentiment
T	A	Around 5 SLA failed	G	Around 5 Major issue reported	A		N
D	G	Around 5 SLA failed	R	No Major incdient reported	G	Vulnerability Remediation plan In place	H
L	R	Around 5 SLA failed	A	No Major incdient reported	R	Vulnerability Remediation plan In place	U

Engagement	MS / PS	RAG	Topic	Owner	Implications	Action Required	Timing	Sequence	Dashboard Visibility
T	MS	A	Topic1	Around 5 SLA failed	G	Around 5 Major issue reported	A	1	Y
D	MS	G	Topic3	Around 5 SLA failed	G	No Major incdient reported	G	2	Y
T	MS	A	Topic2	Around 10 SLA failed	R	Around 50 Major issue reported	R	1	Y