SOLVED

PowerQuery - Editing / Replacing Values

New Contributor

Hello Excel community!

I am very new to Excel PowerQuery and I would really appreciate if you can help me with solving the issue below.

So I have imported the dataset to PowerQuery, all good. Obviously my dataset have a number of null on each column.

I can fill in some of the null values using the data from the other columns and resolve what data goes to those null values, however, I cannot edit the cells. I can replace the values of null, but each individual null will have different values, so I cannot use that option.

 

So, below is a screenshot of part of the dataset - so the City have nulls but I can obviously deduct the City names using the State data.

 

SuziieeWuziiee_1-1646751653933.png

If I cannot replace any data in PowerQuery can I load the data to the worksheet and edit there?

Or, I need to prepare the data first before tidying the data up, then load to PowerQuery?

 

Any help would be so appreciated, thank you!

 

Hope that is clear.

4 Replies
best response confirmed by SuziieeWuziiee (New Contributor)
Solution

Hi @SuziieeWuziiee 

 

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:

DTE_0-1646754252550.png

Split the column by delimiter and use the comma as delimiter:

DTE_1-1646754368711.png

DTE_2-1646754426810.png

Then add a new conditional column:

DTE_4-1646754598524.png

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:

DTE_5-1646754726020.pngDTE_6-1646754749269.png

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.

 

SuziieeWuziiee_0-1646757603349.png

 

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?

Hi @SuziieeWuziiee 

 

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!