Forum Discussion

Helper I

5 years ago

Solved

Combine values into a single cell based on multiple criteria

I really need some help. mahoneypat Anonymous Anonymous edhans v-kelly-msft I have the following table. I need to create a new table whereby if the proj_short_name and Task_code are the same then I...

Need Help

Tutorial Requests

Anonymous

5 years ago

Hi Taffalaffa

Yes, but what is the logic? I did a quick one, only looked at PRED_REL_TYPE, but there is one row different from your desired result, so I guess you have something else to consider

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZNRa8IwEMe/Suiz0K6piI9uIgz2IPjgg/gQ3DkDMYH0BD++Sd1sZ5KTbhUKae6S313u7r/ZZLPdEbJRNnspK7e86xqFUmxujK3d/v7bjro3Jm5ZCqmRffrz+d6KI/hrC6llfWBo2AqFxZafdPSANzfvEJywh+jpDb1CAJWv3ZOHSvsx+/rXPjLtCei88PSTa9BaWsh/uvUhvw7I9vKMJ5uu/5Ry/DnUrerd5CvKEYbyXVoa5TN7M3pnAYEt1PcIRtrKS8Ie4ptsQKgIPFr7Jp+0Jwwwvp73ERQInagzT00S71blFVCk9Rgp6rgoKEfI7SWsirD3QMdnPFnnX7pt8cPr9jE79vgJYQ/Iz9ds71D/bjSt2OiMl5QjDPAkzbYBBtPs9gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROJ_SHORT_NAME = _t, TASK_CODE = _t, Task_Name = _t, PRED_REL_TYPE = _t, Pred_Task_Code = _t, SUCC_REL_TYPE = _t, SUCC_Task_Code = _t, #"Pred Dangler" = _t, #"Succ Dangler" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROJ_SHORT_NAME", type text}, {"TASK_CODE", type text}, {"Task_Name", type text}, {"PRED_REL_TYPE", type text}, {"Pred_Task_Code", type text}, {"SUCC_REL_TYPE", type text}, {"SUCC_Task_Code", type text}, {"Pred Dangler", type text}, {"Succ Dangler", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"PROJ_SHORT_NAME", "TASK_CODE", "Task_Name", "PRED_REL_TYPE"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each if [PRED_REL_TYPE] = "Finish to Start" then "F/S" else if [PRED_REL_TYPE] = "Start to Start" then "S/S" else if [PRED_REL_TYPE] = "Finish to Finish" then "F/F" else if [PRED_REL_TYPE] = "Start to Finish" then "S/F" else [PRED_REL_TYPE]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"PROJ_SHORT_NAME", "TASK_CODE", "Task_Name"}, {{"result", each Text.Combine(_[Custom],",") }})
in
    #"Grouped Rows"

v-kelly-msft

Community Support

5 years ago

Hi Taffalaffa ，

Using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMje0NDAy0gtILShILdJzDlLSUXL0DdDzDvbWMzY1APLcMvMyizMUSvIVgksSi0qUYnWI0ERQlRk5RqNpgrCI00VQlTk5DjInymgLspxtQchsv6AgPUMDEp1NoSaCqgzJMZrOmgiqMiLHaPI1KcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROJ_SHORT_NAME = _t, TASK_CODE = _t, PRED_REL_TYPE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROJ_SHORT_NAME", type text}, {"TASK_CODE", type text}, {"PRED_REL_TYPE", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PROJ_SHORT_NAME", "TASK_CODE"}, {{"ALL", each _, type table [PROJ_SHORT_NAME=nullable text, TASK_CODE=nullable text, PRED_REL_TYPE=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Added Index", "ALL", {"PRED_REL_TYPE"}, {"ALL.PRED_REL_TYPE"}),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded ALL", "Index.1", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each try #"Added Index1"[ALL.PRED_REL_TYPE]{[Index.1]-1} otherwise null),
    #"Grouped Rows1" = Table.Group(#"Added Custom", {"PROJ_SHORT_NAME", "TASK_CODE"}, {{"Max index", each List.Max([Index.1]), type number}, {"All", each _, type table [PROJ_SHORT_NAME=nullable text, TASK_CODE=nullable text, ALL.PRED_REL_TYPE=nullable text, Index=number, Index.1=number, Custom=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"ALL.PRED_REL_TYPE", "Index", "Index.1", "Custom"}, {"All.ALL.PRED_REL_TYPE", "All.Index", "All.Index.1", "All.Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each if [All.Index.1]<>[Max index] then null
 else if [All.Custom]="Finish to Finish"
        then "Start to Start" else "Finish to Start"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"All.ALL.PRED_REL_TYPE", "PRED_REL_TYPE"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Max index", "All.Index", "All.Index.1", "All.Custom"})
in
    #"Removed Columns"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Combine values into a single cell based on multiple criteria.pbix27 KB

Taffalaffa

Helper I

5 years ago

Kelly,

Thank you so much for the help! This is super close to what I need. I have modified what you put and am getting an error that I am hoping you can solve for me. In the custom column you made I need the output to be a string of all the existing relationships for that task. So if all the relationships are "Finish to Start" then it shows "F/S". But if one of the relationships is "Finish to Start" and another for that same task is "Start to Start" then the custom column would show "F/S, S/S" and so on and so forth. But I can't quite figure out how to make that work.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMje0NDAy0gtILShILdJzDlLSUXL0DdDzDvbWMzY1APLcMvMyizMUSvIVgksSi0qUYnWI0ERQlRk5RqNpgrCI00VQlTk5DjInymgLspxtQchsv6AgPUMDEp1NoSaCqgzJMZrOmgiqMiLHaPI1KcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROJ_SHORT_NAME = _t, TASK_CODE = _t, PRED_REL_TYPE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PROJ_SHORT_NAME", type text}, {"TASK_CODE", type text}, {"PRED_REL_TYPE", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PROJ_SHORT_NAME", "TASK_CODE"}, {{"ALL", each _, type table [PROJ_SHORT_NAME=nullable text, TASK_CODE=nullable text, PRED_REL_TYPE=nullable text]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Expanded ALL" = Table.ExpandTableColumn(#"Added Index", "ALL", {"PRED_REL_TYPE"}, {"ALL.PRED_REL_TYPE"}),
#"Added Index1" = Table.AddIndexColumn(#"Expanded ALL", "Index.1", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each try #"Added Index1"[ALL.PRED_REL_TYPE]{[Index.1]-1} otherwise null),
#"Grouped Rows1" = Table.Group(#"Added Custom", {"PROJ_SHORT_NAME", "TASK_CODE"}, {{"Max index", each List.Max([Index.1]), type number}, {"All", each _, type table [PROJ_SHORT_NAME=nullable text, TASK_CODE=nullable text, ALL.PRED_REL_TYPE=nullable text, Index=number, Index.1=number, Custom=nullable text]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"ALL.PRED_REL_TYPE", "Index", "Index.1", "Custom"}, {"All.ALL.PRED_REL_TYPE", "All.Index", "All.Index.1", "All.Custom"}),
#"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each if [All.Index.1]<>[Max index] then null
else if Text.Contains([All.Custom],"Finish to Start") then "F/S"
else if Text.Contains([All.Custom],"Start to Start") then "S/S"
else if Text.Contains([All.Custom],"Finish to Finish") then "F/F"
else if Text.Contains([All.Custom],"Finish to Start" and "Start to Start") then "F/S, S/S"
else if Text.Contains([All.Custom],"Finish to Start" and "Finish to Finish") then "F/S, F/F"
else if Text.Contains([All.Custom],"Finish to Start" and "Start to Finish") then "F/S, S/F"
else if Text.Contains([All.Custom],"Start to Start" and "Finish to Finish") then "S/S, F/F"
else if Text.Contains([All.Custom],"Finish to Start" and "Start to Start" and "Finish to Finish") then "F/S, S/S, F/F"
else "" ),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"All.ALL.PRED_REL_TYPE", "PRED_REL_TYPE"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Max index", "All.Index", "All.Index.1", "All.Custom"})
in
#"Removed Columns"

Forum Discussion

Combine values into a single cell based on multiple criteria

Related content

Sequential Order column based on multiple criteria

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Date difference with multiple criteria