Forum Discussion
Skipping BLANK when summarizing a table
Hi all!
I have a data_table as follows:
| product_1 | product_2 | shipping_day |
| p1 | ... | |
| p5 | ... | |
| p2 | ... | |
| p1 | p5 | ... |
| ... | ||
| p4 | ... |
So there are n different products that are either listet in product_1 or product_2.
I created a new table which contains all products and their shipping days seperately in rows:
New Tabe =
UNION(
SUMMARIZE(
'data_table',
'data_table'[product_1],
'data_table'[shipping_day],
),
SUMMARIZE(
'data_table',
'data_table'[product_2],
'data_table'[shipping_day],
)
)
I would like that entries for which product_1 or product_2 are BLANK are left out. So something like:
New Tabe =
UNION(
IF(NOT(ISBLANK('data_table'[product_1])),
SUMMARIZE(
'data_table',
'data_table'[product_1],
'data_table'[shipping_day],
),
IF(NOT(ISBLANK('data_table'[product_2])),
SUMMARIZE(
'data_table',
'data_table'[product_2],
'data_table'[shipping_day],
)
)
But this doesn't work...
Thanks a lot!
2 Replies
Anonymous , You can use unpivot in power query and remove null or blank string
Here also check that it is not the empty string
NOT(ISBLANK('data_table'[product_2]) && trim('data_table'[product_2]) <> ""
But the problem is that I can not enter the power query editor to transform my data. I only have this option for a data set that I importet. For this table, that I created myself from other datasets this option doesn't appear. How so? The icon of the table is also different. There is not only the table but also the calculator sign.
How can I change the table such that I can edit it in the query editor?
Thanks!
