Combining 2 rows for the same person and keep minimum value only

Anonymous 

There could be an easy way... But this worked for me..

Input:

Output:

Applied steps:

Source - Input

Chaged Type  - 123 - contact no; abc - activity; date - first event date

Pivoted Column - Select Activity and First Event date; Transform -> Pivote column . In advanced options choose "Don't aggregate"

Custom column - Column name is "Custom"

= if [Running] <> null and [Run] <> null and [Running] >[Run] then "Running" else if [Running] <> null and [Run] <> null and [Running] < [Run] then "Run" else null

Reordered column - Drag the custom column next to contact number.

Unpivoted other columns - select contact no, custom columns, Transform ->unpivot columns->unpivot other columns.

Changed Type1 - Custom column to ABC

Added Conditional column

Filtered Rows - selec the column created in previous step and filter for value 0(include 0 and exlude 1)

Removed other columns - Choose columns and choose only contact no, attribute,value.

M query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoqzcvLzEsHshQgwMBQH4iMDAwNlGJ14GoQ8mA1Jhhqgsszc1EUGZjqG1iA1BiC1RhhMQcoD1QFVGIEVmKM4Rw0e4wx7QG51gKqIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Contact Number " = _t, #"   Activity     " = _t, #"    First Event Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contact Number ", Int64.Type}, {"   Activity     ", type text}, {"    First Event Date", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"   Activity     "]), "   Activity     ", "    First Event Date"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each if [Running] <> null and [Run] <> null and [Running] >[Run] then "Running" else if [Running] <> null and [Run] <> null and [Running] < [Run] then "Run" else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Contact Number ", "Custom", "Running", "Run", "Swim"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Contact Number ", "Custom"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Custom", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom] = [Attribute] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom.1] = 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Contact Number ", "Attribute", "Value"})
in
    #"Removed Other Columns"

If it helps ,mark it as a solution

Kudos are nice too

Anonymous -

I used https://community.powerbi.com/t5/Desktop/How-to-do-the-text-containsany-in-power-query/m-p/932611#M446896 and then https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/ as reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoqzcvLzEsHsgwM9YHI0EApVgcuBRI2QRMOLs/MBYmb6htY6BsagsWNEMot9IEyhkZgYWNUC5BNMkYyyRBsElA8FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Contact Number" = _t, Activity = _t, #"First Event Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contact Number", Int64.Type}, {"Activity", type text}, {"First Event Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let SearchString = {"run"} in List.Count(Splitter.SplitTextByAnyDelimiter(SearchString) (Text.Lower([Activity]))) > 1),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Contact Number", "Custom"}, {{"Grouped", each _, type table [Contact Number=number, Activity=text, First Event Date=date, Custom=logical]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Min([Grouped],"First Event Date")),
    #"Expanded Custom.1" = Table.ExpandRecordColumn(#"Added Custom1", "Custom.1", {"Contact Number", "Activity", "First Event Date"}, {"Custom.1.Contact Number", "Custom.1.Activity", "Custom.1.First Event Date"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.1",{"Custom.1.Contact Number", "Custom.1.Activity", "Custom.1.First Event Date"})
in
    #"Removed Other Columns"