Conditional columns break previous columns

[samvdbt]

Hello,

I've encountered a strange error in my PowerBI query editor. I'm working on a dataset that contains multiple codes in a single columns,

I have set up multiple conditional colums to extract these codes from the field and turn them into text in three new conditional colums. Some examples:

if code contains ARG then country becomes Argentina

if code contains MER then car becomes Mercedes

if code contains DSL then type becomes Diesel

However, after adding another conditional colum, completely unrelated to the previous three IF conditions, it somehow breaks the values for the previous columns and makes them all the same value. Is there a limit in the amount of "else if" functions there can be in a conditional column or table?

Any help would be appreciated, thanks!

Sam

[v-shex-msft · Accepted solution]

Hi @samvdbt,

Based on your query formula, you can take a look a below blog which talk about "Multi Condition":

Multi Condition Logic In Power Query

Sample funciton:

let
    SwithCase = (input) => let
    values = {
	{"Model","Result"},//conditions
    {input, "Other"}// the result if not contain the specified string(it must be the last record)
    },
    Result= List.First(List.Select(values, each Text.Contains(input,_{0})= true)){1}
in
    Result
in
    SwithCase

Use :

#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each SwithCase(Text.From([Col1])))

Regards,

Xiaoxin Sheng

[MarcelBeug]

It's hard to tell from the information supplied.

Please share the code from the advanced editor.

[samvdbt]

This is the code from the advanced editor. I've anonimized the source info and changed the IP address. Best to paste in Notepad for formatting. Thanks.

[MarcelBeug]

Thanks for the code supplied.

Unfortunately, I can't think of any logical explanation for this phenomenon.

Especially the value "Generic" in the "Country" column looks strange as this is not any of the values for "Country" in your code.

I could think of referencing a wrong step (so not the previous step but another), but that is not the case.

Some tests/checks I would suggest (but these are just some wild shots):

1. Check if the sort order was changed between the steps..

2. Try and wrap lines of code in Table.Buffer(), so the result of these steps are stable tables.

    Note: in general, Table.Buffer ruins any query folding and thus may influence performance so it should be used with care.

3. Try and comment/decomment parts of the code repeatedly to find out when the phenomenon occurs / does not occur.

Otherwise I can only think of some technical issue.

Maybe software should be reinstalled.

[samvdbt]

Thanks for the reply.

I"ve found that moving the "Country" column to the last step allows all of the other steps to work, except the country step. This is the one with the most else if functions. Could there just be a limitation in the amount of else if conditions that can be included in a single query?

[v-shex-msft]

Hi @samvdbt,

>>Could there just be a limitation in the amount of else if conditions that can be included in a single query?

I haven't found any document mention about the length limit of if statement .(I find the limit of the query size: 64K characters)

Perhaps you can take a look at below article:

Power Query specifications and limits

Regards,

Xiaoxin Sheng