Calculating a new column by grouping the values

[alawode]

Hi Experts,

I have the following Products Column (attached), and i am trying to create a new calculated column that will have car names as "Cars", the Blank values as "Blank" and everthing else shold be called "BIKES".  so basically just 3 unique vales in the new column .Please whats the best expression to achive this?

Thanks in advance

[Jakinta]

If your car models are always upper cased, and bikes are in proper casing then you can use this trick:

= Table.AddColumn( YourProiorStepName, "Custom", each if [Products] = Text.Upper([Products]) and Text.Upper([Products])<>"BLANK" 
then "Cars" else if Text.Upper([Products])= "BLANK" then "Blank" else "BIKES")

Otherwise you would have to create some Table(s) or List(s) with cars and bikes models then perform some merging or testing on containment. There are plenty of threads on that topic on forum...

[alawode]

Im basically trying to create a case statement that will create the calcualted column, is there no way to do this in Power BI?

[mahoneypat]

Yes.  There is a simple way to do it w/ no code, using a Groups column.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Docs

Pat

[alawode · Accepted solution]

I am trying to use the calculatd colmn as a filter so the "Binning" solution didnt work. I ended up using the Switch Function. Thanks