none
M Power Query: Removing entire rows based on condition RRS feed

  • Question

  • I want to remove entire rows IF both horizontally adjacent cells in the last two columns are empty (hence rows 4, 6, 7, 9 should be removed). How can I do this? Not sure by the way whether I should do this in M or DAX. Any help appreciated. Thanks!


    Rafael Knuth



    Thursday, March 1, 2018 7:07 AM

Answers

  • Hey Rafael, 

    Considering that this an ETL task I would recommend to do the data mashup in M.

    You can start by removing the empty records in the table and then adjusting the formula

    Which results in a formula like

    = Table.SelectRows(
    	#"Your Table", 
    	each not List.IsEmpty(
    		List.RemoveMatchingItems(
    			Record.FieldValues(_), 
    			{"", null}
    	)))

    And you have to modify the List.RemoveMatchingItems with this

    = Table.SelectRows(
    	#"Your Table", 
    	each not List.IsEmpty(
    		List.RemoveMatchingItems(
    			{[Column1],[Column2]}, 
    			{"", null}
    	)))

    Where the Column1 and Column2 are the corresponding names of the columns you want to consider for this filtering.

    Let us know if you need something more generic.

     



    Thursday, March 1, 2018 9:55 AM