Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
It's hard to tell from the information supplied.
Please share the code from the advanced editor.
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.
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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |