Forum Discussion
using [TableAction.DeleteRows]
Hello everyone, a little help in here.
i have a table.selectrows(#renamed columns2, each ([REFER] <> "1610"))
What i want to do is DELETE [REFER] = 1610, because its incomplete, and i will need to append 1610(oct/2016) in a few days. because of this situation i need to remove the incomplete month of my table or i will create duplicates of everything on october when trying to add the data.
Table.SelectRows will only filter the table rows. To remove the specific rows, we can first add an conditional column to mark the rows which equal 1610. Then sort this mark column and delete the first N rows. At last we just need to delete the mark column.
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [REFER] = 1610 then "delete" else null ), #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Descending}}), #"Removed Top Rows" = Table.Skip(#"Sorted Rows",2), #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Custom"})Best Regards,
Herbert
5 Replies
The description of what you want to achieve is clear, but your post lacks a question.
At first glance, your expression looks fine.
How can we help you?
Oh sorry, the table.selectrows is what i managed to do. i want to delete the refer = 1610
Maybe is my lack of understanding, but.
1- i have a incomplete month oct/2016 (refer=1610) excluded on a table.selectrows filter
2-if i append a complete oct/2016 table on my query, it will not duplicate with the fields filtered on the select row command?? i mean, i will have to remove the filter when i have the oct/2016 complete table in a few days.
I would like to use "TableAction.DeleteRows" to remove anything from my query that have REFER = 1610, but im not being able to use the command properly.
I had never heard or read about these Action functions before your question and just read more about them on the MSDN web site.
The query editor in Power BI tells me TableAction.DeleteRows is not a recognized name (a.k.a not implemented yet), but maybe I missed an update or something.
Table.SelectRows will only filter the table rows. To remove the specific rows, we can first add an conditional column to mark the rows which equal 1610. Then sort this mark column and delete the first N rows. At last we just need to delete the mark column.
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [REFER] = 1610 then "delete" else null ), #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Descending}}), #"Removed Top Rows" = Table.Skip(#"Sorted Rows",2), #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Custom"})Best Regards,
Herbert
Really thks Herbert_liu, that solves the problem for me :)
