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
Anonymous
Not applicable

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

Hi, 

Here's what my data looks like

Contact Number    Activity         First Event Date

1                             Running        01/01/2010

1                             Run               04/01/2010

1                             Swim             05/08/2011

2                             Run               08/05/2012

3                            Running        04/01/2010

3                            Swim             01/08/2010

 

What I want to do, is if a person both has a 'Run' and a 'Running' category, I only want to keep whichever they did first. So in the above table, I would only want to keep '1   Running   01/01/2010' and remove the second row for that person.

The other two people I have both only have one 'run' or a 'running' activity, so they don't need to be combined. 

How do I go about doing that?

Thanks!!

 

2 ACCEPTED SOLUTIONS
VasTg
Memorable Member
Memorable Member

@Anonymous 

 

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

 

Input:

image.png

Output:

image.png

 

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.

image.png

Changed Type1 - Custom column to ABC

Added Conditional column

image.png

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

 

 

Connect on LinkedIn

View solution in original post

ChrisMendoza
Resident Rockstar
Resident Rockstar

@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.

 

5.png

 

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"





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!

Proud to be a Super User!



View solution in original post

2 REPLIES 2
ChrisMendoza
Resident Rockstar
Resident Rockstar

@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.

 

5.png

 

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"





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!

Proud to be a Super User!



VasTg
Memorable Member
Memorable Member

@Anonymous 

 

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

 

Input:

image.png

Output:

image.png

 

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.

image.png

Changed Type1 - Custom column to ABC

Added Conditional column

image.png

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

 

 

Connect on LinkedIn

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