Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Skipping BLANK when summarizing a table

Hi all!
 
I have a data_table as follows:
product_1product_2shipping_day
p1 ...
p5 ...
 p2...
p1p5...
  ...
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! 
Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!