Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Remove blank row from calculated table based on filter

I have a table of key-value pairs named Entity and Role. Entities are companies participating in a transaction. Roles fall into two groups, buyers and sellers. For buyers, the entity field always ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Thanks FreemanZ 

    This works for the dim_buyer table, but I still get a blank row in the dim_seller table.
    I double-checked the field values, and there are none with a space or other non-printable character. I also retried replacing blanks with null in Power Query Editor but the blank row remains.

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi Anonymous 

try like:

dim_buyer =
CALCULATETABLE (
   DISTINCT( Data[Entity]),
   FILTER( Data, 
      CONTAINSSTRING( Data[Role], "buyer")
   )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks FreemanZ 

This works for the dim_buyer table, but I still get a blank row in the dim_seller table.
I double-checked the field values, and there are none with a space or other non-printable character. I also retried replacing blanks with null in Power Query Editor but the blank row remains.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    how about replacing VALUES with DISTINCT as well, like:

    dim_Seller = 
    CALCULATETABLE (
       DISTINCT(Data[Entity]),
       FILTER( Data,
          NOT(CONTAINSSTRING( Data[Role], "buyer"))
      )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      That's what I did, but I was still getting a blank row.

      I think the problem is due to the fact that there are blank or null rows in the table, so if I say "does not contain the string 'buyer'", it will match.


      After thinking about the data a bit more, the rows with no buyer or seller are not meaninful. I filtered those out, and now there is no more blank row in the seller table.

       

      Thanks very much for your help. I will mark your response as a solution.

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        Another occasion for unexpected blanks:

        if a table is

        1) on the one side of a relationship and 

        2) does not have all the values in the related colunn from the many side

         

        a blank value will be added automatically.