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.
Solved! Go to Solution.
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!
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
Thank you very much for your help. I really appreciate. You saved my life🤗. Thanks a lot!
All your solutions work very well. Thank you again!