Conditional Column - Nested IF limit

[Anonymous]

Hi,

I ahve a coditional column with the following

#"Added Conditional Column" = Table.AddColumn(dbo_DFPWeekly, "Title", each if Text.Contains([Ad unit], "News and Star") then "News and Star" else if Text.Contains([Ad unit], "Hexham Courant") then "Hexham Courant" else if Text.Contains([Ad unit], "Cumberland News") then "Cumberland News" else if Text.Contains([Ad unit], "Whitehaven News") then "Whitehaven News" else if Text.Contains([Ad unit], "The Mail") then "The Mail" else if Text.Contains([Ad unit], "Go Active") then "Go Active" else if Text.Contains([Ad unit], "In Cumbria") then "In Cumbria" else if Text.Contains([Ad unit], "Clickin2") then "Clickin2" else if Text.Contains([Ad unit], "Pets") then "Pets" else if Text.Contains([Ad unit], "Clickin2Findit") then "Clickin2Findit" else if Text.Contains([Ad unit], "Eskdale") then "E&L" else if Text.Contains([Ad unit], "Live") then "Cumbria Live" else if Text.Contains([Ad unit], "CN Jobs") then "CN Jobs" else if Text.Contains([Ad unit], "Times and Star") then "Times and Star" else null)

Now there are still some more conditions to add but there seems to be a limit...?

This is not very helpful?  Any ideas/ways round it?  Possibly two statements?

[dmark]

I got the same problem and although it seems it's from SQL from that error, I believe it's a limitation with PowerBI December update instead.

If you go thru your Applied Steps highlighting the steps one after the other, the error does not occur (in my case) from the import of the source but more at the applied step where the Conditional Column was created that has more of a nested statement of 10.   Another verification for me, was if I reduce it below 10 the problem went away.  Mind you these steps occur after import of data to powerbi.

For me anyways, I reverted to the November version and the problem went away completly without making any changes.

[coiter]

then they should offer a CASE WHEN/THEN of Conditional Columns instead of a nested IF when they did the december update.

I created custom views with CASE statements in the sql database, so i didnt have to bother with future powerbi issues next time they update.

[v-qiuyu-msft]

Hi @Anonymous,

I guess you get data from SQL database, right? 

When get data from SQL database, if conditions can't be more than 10 levels. If you enter data not get data from the SQL database, you can see you can add more than 10 levels conditions. 

This is a limitation just as the error points out: 

Best Regards,
Qiuyun Yu 

[coiter]

I have the same problem myself, and this conditional column came with the december update of powerbi desktop, i had no problem with earlier versions of powerbi desktop and this nesting.

the nesting is done in powerbi desktop after import

[v-qiuyu-msft]

Hi @coiter,

Did you get data from SQL database as well? Please make sure the condition level less than 10. 

Best Regards,
Qiuyun Yu 

[coiter]

Yes, i got the data from SQL database as imported data.

and i run this Nested IF as a conditional column inside Powerbi, and have been running this for over 6 months without any problems. So saying its SQL that is the problem doesnt make any logical sense, when it happens after upgrading to PowerBI December update.

so what changed from November to December to make this a problem, because my sql server is the same as before.

[Anonymous]

The error I get is 

    Message=Case expressions may only be nested to level 10.