Mixed Data for States Column

Ok, so your original question is more

"I have state names for some rows and state abbreviations for other rows, in the same column.  I want to replace the state names with the correct abbreviations".

If so, you will need to get yourself a table with state names and abbreviations (a master table).  This could be from a spreadsheet or from a webpage - there will be lots around. Import that table.

You then need to do a merge with the existing table and the new 'States' table. This will be a left join on the 'ship to state' column and the statename column in the 'States' table.  You can then return the state abbreviation in a column. 

Some of them wil be null because the existing column has abbreviations already, or spelling errors or different variations of the name won't bring a match back.  You can tidy up the data at this stage to make matches more likely.

At that point you can add a column that says 'if returned column is null then original column else returned column'

--

So, quite a lot there for people who are new to Power Query but all very possible.  I think the powerbi documentation has a similar example (from memory).  I'll check and post the link. 

Are all the fields in  abbreviation, StateName   format and you want to return only the abbreviation i.e. the portion to the left of the comma?

Good Morning HotChilli ! 

Unfortunatley, the data is both state name and state abbreviation. I want to make it so that they are all state abbreviation. 

Hello Anonymous ,

Is that a 'yes' on my question?

Anonymous suggest you split column on delimiter (,). You can do this in Power Query by right-clicking the column header and choosing that option.