delete rows based on conditions

[Anonymous]

Hi 
Customers who start their journey before 10 am and End their journey after 7:30 pm from Sat-Wed - these should be removed along with their entire journey steps

below is my sample data and deletion should be dynamical. (when every the data set is refreshed and if this condition is satisfied then that rows should be deleted )


customerid    date                        start                                        end                                          zone              destination
1                     2020-01-03          2020-01-03 00:01:00:000        2020-01-03 02:30:12:1234        xzy                 india

1                     2020-01-04         2020-01-04 00:01:00:000         2020-01-03 02:30:12:1234        play                india
1                     2020-01-04         2020-01-04 10:45:00:000         2020-01-04 11:30:12:1234        play                india

1                     2020-01-09         2020-01-09 00:01:00:000         2020-01-09 02:30:12:1234        play                india

3                     2020-01-14         2020-01-15 11:45:25:852         2020-01-15 14:45:23:753          Play                india


Here row 2 should be deleted because that row journey start at play before 10:00 am day is sat 

thanks in advance 

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the Power Query Editor to meet your requirement.

1. Add a custom column based on [date] and configure it as week format.

2. Then add two custom columns to extract time in [Start] and [End].

#time(Time.Hour([start]),Time.Minute([start]),Time.Second([start]))

#time(Time.Hour([end]),Time.Minute([end]),Time.Second([end]))

 

3. We can create a Customer Filter based on [date] column.

4. At last we can remove the three custom columns.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , go to data transformation and filter the data for that column.

[AllisonKennedy]

Do you want this deleted completely from the data? If so, do it as a filter in the Power Query Editor. You can add columns for Day of Week based on start and end by using the buttons in the Add Column tab in the ribbon. Add column for the  start Time and end Time as well (again using the buttons in the ribbon). Merge the start time and start day of week. Merge the end time and end day of week. Then filter the merged columns according to your criteria.

All these changes will happen every time you refresh the report data.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

@AllisonKennedy  Here my data set is very large, so I have to delete that entire row based on the conditions. and I have multi-condition for the same column, if the zone name is different from play there are other conditions to remove. so in my case, I think data filtering is not the best option for me. so can you suggest me how to delete the rows?

[AllisonKennedy]

@Anonymous  Especially with large datasets filtering is the best way to delete the rows. You can use custom M code to delete based on multiple criteria, or try exploring the advanced filtering options from the drop down for the column header. You can even create a conditional column in Power Query (this is made very easy with the Add Column tab in the ribbon > Conditional Column) and put all your conditions into there for what should be removed, have the value of the new column set to 'Remove' and have the value of anything not to remove set to 'Keep'. Then filter that new column to remove all rows equal to 'Remove'. 

Filtering is definitely the way to go to delete rows.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv