Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.