Remove row in calculated table based on CONCATENATE field

[IvanS]

Hi guys,

in my data model I am calculating nr. of tasks where task owner can select team composition (unlimited nr. of people participating on task). Calculated table below is splitting 1 task ID into several rows if field Team Composition is not blank.

VAR _table = FILTER(Working time, Working time[ClosedTime]>BLANK())
RETURN
FILTER(
    DISTINCT(    
        UNION(
            SELECTCOLUMNS(_table,"Task ID", Working time[ID],"Close Date", Working time[ClosedTime],"Name", Working time[TaskOwner Name], "Task performed as", "Task Owner"),

            SELECTCOLUMNS(_table,"Task ID", Working time[ID],"Close Date", Working time[ClosedTime],"Name", Working time[TeamComposition], "Task performed as", "Team Composition")
        )
    ),
    [Name] <> BLANK()
)

However, I found cases where used accidentally put the same name (Task Owner = Team Composition) and such cases need to be filtered out from this table.

What I am trying to achieve is to remove duplicates and keep only distinct values for expression CONCATENATE( [ID], [Name]). Unfortunately I was not able to include it in my table calculate.

Any suggestions are much appreciated!

Thanks

Ivan

[bolfri]

Some sample row(s) of your data would be nice and expected results based on that data. 

[IvanS]

Hi @bolfri ,

please find below steps how the calculated table is created.

1. Original table

ID	Close Time	TaskOwner Name	Team Composition
1	1.1.2023	User 1	User 1	/* error: TaskOwner Name = Team Composition */
2	31.1.2022	User 2	User 3, User 4

2. Calculated table

ID	Close Time	Name	Task Performed as
1	1.1.2023	User 1	Task Owner	/* row has been split to 2 rows */
1	1.1.2023	User 1	Team Composition	/* duplicated row */
2	31.1.2022	User 2	Task Owner
2	31.1.2022	User 3	Team Composition
2	31.1.2022	User 4	Team Composition

Now as you can see - Task with ID have incorrectly inputed Team Composition with same name as TaskOwner name. Therefore, for these cases I need to remove this row and keep only that row where Task Performed as is "Task Owner".

Desired result is following:

ID	Close Time	Name	Task Performed as
1	1.1.2023	User 1	Task Owner	/* duplicated row removed */
2	31.1.2022	User 2	Task Owner
2	31.1.2022	User 3	Team Composition
2	31.1.2022	User 4	Team Composition

Hope it is more clear now.

Thank you

Ivan

[bolfri · Accepted solution]

Based on your data it's better to do that in Power Query M than in DAX. It's less complex and gives you a flexibility.

This is a few steps how it could be done in Power Query M editor:

Oryginal Data:

Step 1. Select "Team Composition" column and select from Ribbon > Transform > Split Column by Delimeter.

Selec or ener delimeter: --Custom--

Value: ", " <-- here is a coma AND SPACE (white character)

Split at: Each occurrence of the delimeter

and in Advanced options: Split into Rows

Step 2. Create a new Custom Column that checks invalid inputs from user such us "TaskOwner"="Team Composition".

Step 3. Tricky part. You can do that step in same query, but to show you the output I will use a new query and them Append the Queries, so you can track each step.

3. Power Query - multiple steps 🙂
#if you want, I can show you how to do that in same query

Table 1. Task Owners

We know that Task Owner should be an aggregation to get unique Owners per 

Create new Empty Query and use Power Query M groupping functions:

= Table.Group(Table, {"ID", "Close Time", "TaskOwner Name"},{})

Rename column [TaskOwner Name] to [Name]

Then Add a new column "Task Performed as" with "Task Owner" value.

This is the result for: TaskOwner table.

Full Power Query M for TaskOwner:

let
    Source = Table.Group(Table, {"ID", "Close Time", "TaskOwner Name"},{}),
    #"Rename column: TaskOwner Name to Name" = Table.RenameColumns(Source,{{"TaskOwner Name", "Name"}}),
    #"New column: Task Performed as" = Table.AddColumn(#"Rename column: TaskOwner Name to Name", "Task Performed as", each "Task Owner")
in
    #"New column: Task Performed as"

Table 2. Task Composition

To create second table we need to filter out rows with errors and then group them like before but this time with Team Composition.

Create new Empty Query and use Power Query M groupping functions:

= Table.Group(Table.SelectRows(Table, each ([Error Flag] <> true)), {"ID", "Close Time", "Team Composition"},{})

Rename column [Team Composition] to [Name]

Then Add a new column "Task Performed as" with "Team Composition" value.

This is the result for: TaskComposition table.

Full Power Query M for TaskComposition :

let
    Source = Table.Group(Table.SelectRows(Table, each ([Error Flag] <> true)), {"ID", "Close Time", "Team Composition"},{}),
    #"Rename column: Team Composition to Name" = Table.RenameColumns(Source,{{"Team Composition", "Name"}}),
    #"New column: Task Performed as" = Table.AddColumn(#"Rename column: Team Composition to Name", "Task Performed as", each "Team Composition")
in
    #"New column: Task Performed as"

Table 3. Combined Table by Append Queries as a New Query with two previous tables.

let
    Source = Table.Combine({TaskOwner, TaskComposition})
in
    Source

Note that in all step(s) I've skipped changing type of fields. It's better to do that in the final table due to fact that you will do that once, not 3 times. 🙂

I hope that this will help you.

PBIX FILE:

https://we.tl/t-j6UlDwxJSD