Find value in column a, set value in column b

[nixonyx]

I have a list of service desk incident records. There is some missing data that occurs when the customer cannot be identified (or does not need to be identified).

Currently, when reporting, we find all entries with the user name "unknown user" and set their department and team to "unknown user".

I have an excel macro to do this as part of a wider macro to transform this and other source data into the final report. We are trialling PowerBI as a more efficient way of achieving this.

How would I perform this action in PBI? I am struggling to find good code examples for the advanced query editor so don't understand the syntax being used.

Thanks for any/all suggestions.

[Greg_Deckler · Accepted solution]

The "easy" way to do this would be to create a new column like Department1 in the query editor and use a formula like:

= if [User Name] = "unknown user" then "unknown user" else [Department]

[nixonyx]

That simple? Feel silly now for not thinking of that, having created other custom columns. Thank you

[nixonyx]

Ok, not quite solved.

This is my syntax, which reports no errors when I type it into the new custom column box:

IF([Customer]="Unknown User","Unknown User",[SoV Account Type1])

In the advanced editor it look like (still with no syntax errors):

#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each IF([Customer]="Unknown User","Unknown User",[SoV Account Type1]))

However, I cannot view the results as I get this error when I save the custom column/close advanced editor:

"Expression error: The name 'IF' wasn't recognised. Make sure it's spelled correctly."

Is there an issue with using DAX formulae in the advanced editor?

[Greg_Deckler]

Can't use DAX in Power Query, DAX is only used after the data gets into the model. Your syntax for DAX is correct, my syntax for "M" (Power Query) is correct for the query editor.

This is the reference for "M"

https://msdn.microsoft.com/en-us/library/mt211003.aspx

This is the reference for DAX

https://msdn.microsoft.com/en-us/library/ee634396.aspx

[nixonyx]

I will try again. I did your version first but that didn't work so I looked for if functions used in power bi which lead me to DAX.

I cannot find an IF function in the M query specification but have to admit I haven't clicked on every category.

I will try again.

[nixonyx]

Ok, must have been a typo the first time. Sorry to have been a pain

Thank you for your help.