How to merge two columns but removing N/A?

[longlongisi]

Hi 

i would like to merge  two columns- Ingredient Category & Additive Category into one, but removing the N/A.How can i do it? Thanks

[longlongisi]

Thanks @PijushRoy 

[PijushRoy]

Hi @longlongisi 

You want to do in Power Query or DAX

If Power Query, 1st replace the N/A value to Blank and merge Custom Column = [Ingredient Category]&[Additive Category]

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[longlongisi]

@PijushRoy Thanks, but i dont want to change the N/A to Null, is there any alternative solution?

[PijushRoy · Accepted solution]

Hi @longlongisi 

Create conditional column in Power Query
or 
with DAX

= SWITCH(
TRUE(),
Table[Ingredient Category] = "N/A",Table[Additive Category],
Table[Additive Category] = "N/A",Table[Ingredient Category])

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin