Forum Discussion
PowerQuery - Editing / Replacing Values
that's right, you cannot edit individual cells in Power Query.
One option (you named it already), would be to fill the missing cities already in the data source, before you load it into Power Query.
If that's not possible (e.g. data comes from an external database) and the information is also included in another field (le.g. in the State field), then you can try to extract it from there.
How to do it in detail depends on the pattern that you have in the other field. I understand that some of the states include the city name and some don't.
Let's say the state contains the city and it's separated by a comma from the state:
Split the column by delimiter and use the comma as delimiter:
Then add a new conditional column:
If the existing City column is not empty, it takes that value. Otherwise it takes the value of the State.1 column.
Then add another conditional column:
Finally, delete the old columns and rename the new ones.
Hope, this helps a bit.
Hello Martin_Weiss ,
Really appreciate you explaining this. Now would remember that!
I thought I would share you another screenshot of the data set to show how the data is like.
This is how the data is like, I have manually filled in the City so the one I have null is the State.
What would be the best way of solving this? A conditional formatting too like the one you have shown or is there another?
Is there a way for me to tell Excel that if this City is equals to that it should be that State? Hope that makes sense?
the difficulty here seems to be, that you have a mix of information in the City field. Sometimes it's the city and sometimes it's the state. And only if the City contains the state, you would want to take that value in the state field.
One way to solve this would be a list of all states (in a separate table). This list could be loaded into Power Query and than you could combine these two tables with a so-called join. This means, you would link the City-field in your data table to the state field of your new state table. And if there is a match, you could have the state from the state table in a new column.
After this, you could add conditional columns like in my first reply.
Many thanks,Martin_Weiss for all the suggestions and also clearing my questions about best to clear nulls/blanks before loading to PowerQuery.
I managed to take advantage of the Geography Data Type under Data type.
I was missing some information but have managed to played around with it.
Have a good day!