Forum Discussion

IvanS's avatar
IvanS
Helper V
3 years ago
Solved

Remove row in calculated table based on CONCATENATE field

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

 

Need Help
Tips and Tricks
  • bolfri's avatar
    bolfri
    3 years ago

    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

     

3 Replies

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

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

    • IvanS's avatar
      IvanS
      Helper V
      3 years ago

      Hi bolfri ,

      please find below steps how the calculated table is created.

       

      1. Original table

      IDClose TimeTaskOwner NameTeam Composition 
      11.1.2023User 1User 1/* error: TaskOwner Name = Team Composition */
      231.1.2022User 2User 3, User 4 

       

      2. Calculated table

      IDClose TimeNameTask Performed as 
      11.1.2023User 1Task Owner/* row has been split to 2 rows */
      11.1.2023User 1Team Composition/* duplicated row */
      231.1.2022User 2Task Owner 
      231.1.2022User 3Team Composition 
      231.1.2022User 4Team 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:

      IDClose TimeNameTask Performed as 
      11.1.2023User 1Task Owner/* duplicated row removed */
      231.1.2022User 2Task Owner 
      231.1.2022User 3Team Composition 
      231.1.2022User 4Team Composition 


      Hope it is more clear now.

       

      Thank you

      Ivan

      • bolfri's avatar
        bolfri
        Solution Sage
        3 years ago

        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