Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Delete rows if blank field

I created two columns related to another data table. I need to delete rows with blanks. I go to query editor and I do not see the columns I created so I can filter out the rows with blanks in the new columns. How can I delete or filter out the rows with blans in those fields?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Anonymous

    If you want to filter out blank row in report view, you can set "show item when the value is not blank" in Visual Filter container.

     

    If you want to change the data model with no blank value from column related from other tables.

    You could use the formula to create a new table.

    table = FILTER(ALL(Sheet2),[Column]<>BLANK())

     

    Best Regards

    Maggie

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    8 years ago

    Did you created the column using DAX ? if yes then you cannot see it in query editor. You can filter the records in query editor so that it doesn;t bring those blank rows to data model, let me know if need further assistance.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you,

       

      I clicked Modeling/New Column then named the column and used RELATED(Dept[Department]). So how do I filter it if I can't see the column in the data when I go to Query Editor? Sorry I am new to analytics and to Power BI.

       

       

       

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        8 years ago

        In that case you can drop this field on page level filter and choose "is not blank", to understand various filters, read this article

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Just to mention - Power BI cant delete any record, and you may aware of this. Basically, you wanted to filter the blank records.

    As parry2k said, please check where you have created the new columns  , is it done in DAX or in Power Query.

     

    If its in DAX- you may need to use ISBLANK() function to filter the records.

     

     

    Thanks

    Raj

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Anonymous

    If you want to filter out blank row in report view, you can set "show item when the value is not blank" in Visual Filter container.

     

    If you want to change the data model with no blank value from column related from other tables.

    You could use the formula to create a new table.

    table = FILTER(ALL(Sheet2),[Column]<>BLANK())

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Yes! Thank you very helpful!