Forum Discussion
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 contains the string 'buyer'.
I'm able to create a dimension table for buyers as follows:
dim_buyer =
CALCULATETABLE (
VALUES( Data[Entity]),
FILTER( Data,
CONTAINSSTRING( Data[Role], "buyer")
)
)
The resulting table includes a blank row that I want to eliminate.
In addition, I want to create a companion table that includes sellers. This is defined as distinct values of Data[Entity] where Data[Role] does NOT contain the string "buyer", so that's easy enough:
dim_Seller =
CALCULATETABLE (
VALUES(Data[Entity]),
FILTER( Data,
NOT(CONTAINSSTRING( Data[Role], "buyer"))
)
)
I also do not want this table to contain a blank row.
I've tried to filter the result of the CALCULATETABLE to eliminate the blank rows, but I don't have a column name. I tried ADDCOLUMNS in place of VALUES, but then I get the whole table.
Any help is very much appreciated.
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.
5 Replies
FreemanZSuper User
hi Anonymous
try like:
dim_buyer =
CALCULATETABLE (
DISTINCT( Data[Entity]),
FILTER( Data,
CONTAINSSTRING( Data[Role], "buyer")
)
)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
how about replacing VALUES with DISTINCT as well, like:
dim_Seller =
CALCULATETABLE (
DISTINCT(Data[Entity]),
FILTER( Data,
NOT(CONTAINSSTRING( Data[Role], "buyer"))
)
)