Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Taffalaffa
Helper I
Helper I

Combine values into a single cell based on multiple criteria

I really need some help. @mahoneypat @watkinnc @Vera_33 @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 can have a column that combines all the Pred_Rel_Type for that task code in one cell (Pred_Rels).  For example in the box I have outlined, instead of that being two lines it would be one line and the Pred_Rels would show F/S, F/F.  Can someone please help me!! 

 

Here is a link to the sample data and my desired result:

Combine values into a single cell based on multiple criteria.pbix 

 

 

Taffalaffa_0-1626811670124.png

 

1 ACCEPTED SOLUTION

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

 

Vera_33_0-1627348871128.png

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"

 

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1627024395373.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

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"

 

 

edhans
Super User
Super User

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Taffalaffa 

 

Agree with @edhans, you need to read the articles and understand how to ask your question...I don't understand what you want...you need to provide sample data and expected results in a format which everyone can copy (use Excel to generate some dummy data)

Got it!  Thank you for the feedback! I have updated my question so hopefully it is clearer and provided sample data and my desired result.

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

 

Vera_33_0-1627348871128.png

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"

 

This is fantastic! Thank you so much for your help! And great catch on the desired result that was incorrect.  That was a mistake in my part!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors