Combine Rows and show in a Table

[gssarathkumar]

Hi,

I have the below two tables in my source.

Table1:

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

Table2:

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

I made a connection between the tables based on Engagement column.

I would like to create a table visual with these columns.

My current output:

My Expected Output:

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.

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.

Kindly help me to get the same.

@amitchandak @Greg_Deckler @lbendlin @DesktopOwl @Goodlytics4U @HelpMe @Ahmedx @v-huijie-msft 

[v-linyulu-msft · Accepted solution]

Thanks for the reply from amitchandak , please allow me to provide another insight:

Hi, @gssarathkumar 

We currently have two solutions, each with its own drawbacks. You can choose based on your needs:

1.Firstly, you can merge the two tables and create an index, keeping only the first one:

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 & Obligation Remarks", "Stability & Availability Remarks", "Security & Vulnerability Remarks", "Customer Sentiment", "Table (2).Topic", "Table (2).Owner", "Table (2).Implications", "Table (2).Action Required", "Table (2).Timing", "Index"}, {"Count.SLA & Obligation Remarks", "Count.Stability & Availability Remarks", "Count.Security & 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:

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.

Please find the attached pbix relevant to the case.

 
Of course, if you have any new ideas, you are welcome to contact us.
 

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.

[amitchandak]

@gssarathkumar , On Matrix visual as of Now conditional formatting is not supported on Row/Column Fields. And Combining of rows is only available with Row fields