Hi, I am cleaning a dataset, and I noticed that there are some cells with a null value while other cells have some value under the same condition. For example: CountryGDPGermany10000000GermanyNullGermany10000000France20000000FranceNull I need to replace the null value with the GDP of each country, like this: CountryGDPGermany10000000Germany10000000Germany10000000France20000000France20000000 How can I do that? There are many countries in this situation. Thank you for your help.

Filling of cells

Hi,

I am cleaning a dataset, and I noticed that there are some cells with a null value while other cells have some value under the same condition. For example:

Country	GDP
Germany	10000000
Germany	Null
Germany	10000000
France	20000000
France	Null

I need to replace the null value with the GDP of each country, like this:

Country	GDP
Germany	10000000
Germany	10000000
Germany	10000000
France	20000000
France	20000000

How can I do that? There are many countries in this situation.

Thank you for your help.

Datasets

Accepted answers

ed_hayter

I opted for a filter out Nulls create a lookup table with a GDP for every country that is nonnull (Logic was to take the first value but you could use different logic if you have a choice of values). Then join onto the NULL cases and replace Null GDP with the populated one. Union the filled data with the original data used for the lookup table for the expected outcome

Lookup and Join.yxmd

All comments

DataNath

Hey @Candykobli, there's a few ways you could do this i.e. taking a max with a Summarize and re-joining. However, I think this is a little simpler:

1) Sort values (within [Country]) in descending order so the non-null value is always first

2) Use a Multi-Row Formula to fill down

Hope this helps!

Fill null GDP values.yxmd

ed_hayter

Lookup and Join.yxmd

Candykobli

Thank you very much for your help. I really appreciate. You saved my life🤗. Thanks a lot!

FinnCharlton

If you love macros you'll love this solution

Filling Rows.yxzp

DataNath

Here's another way of going about it where we just take the count of records and the max GDP per country (so it removes the nulls). After that, we just generate the number of rows necessary for each Country:

1232.png

Candykobli

All your solutions work very well. Thank you again!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703