Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time 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!!
Solved! Go to Solution.
@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"
Proud to be a Super User!
@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"
Proud to be a Super User!
@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