Limit to outputs in Power Query or DAX?

[Anonymous]

Hi All,

I see to be having the same problem whether I try to create a conditional/calculated column either in Power Query or with DAX.

The issue is I am trying to create a column with about ~38 catergoies based on text within the product name of a product (the product I am trying to analyse changes product code every quarter, but the product name largely carries over).

So for the above, my DAX formula has five separate "Flash Sale" categories, but for some reason only three of these are being picked up and two are mssing. The same thing happens for "Easy As" (two of the six are missing). I encountered the same problem when using the Add Conditional Column in Power Query. 

Does anyone know why this happens?

Thanks!

[TheoC · Accepted solution]

Hi @Anonymous, have you confirmed that each of the strings you are evaluating actually contain the search text you're looking for?  In particular, if there are regular changes to the product code, even though the product name may often carry over, there may be a minute change that impacts this.  

Can you try the following:

1. Confirm that there are records that match the two missing products for each key category; and

2. Adjust your DAX to include "Flash" instead of "Flash Sale" and "Easy" instead of "Easy As"

Also, although it's not what you have requested, there may be value in breaking down the products into Key Category and Subcategory columns / fields to allow you to better assess the data (i.e. Flash Sale may have 100 products of which each subcategory has 40, 20, 20, 5 and 5 products).

Hope to hear how it goes!

Theo 🙂

[Anonymous]

Thanks @TheoC !

I broke up the query into smaller parts and that seemed to work, even though the text in the statement was the same. I'm kind of stumped by this by using a few columns to achieve what I want will be fine.

[TheoC]

@Anonymous that is great to hear, mate!  I honestly am unsure why it does that but I have experienced the problem myself and found that breaking it down into smaller pieces works for some reason.

Anyway, all the best mate and well done!