I am trying to clean data received from an Excel file and transform it using PowerQuery (in PowerBI) into a useable format. Below a sample table, and what I am trying to do:
| Country | Type of location |
|--------- |------------------ |
| A | 1 |
| | 2 |
| | 3 |
| B | 1 |
| | 2 |
| | 3 |
| C | 1 |
| | 2 |
| | 3 |
As you can see, I have a list of location types for each country (always constant, always the same number per country, ie each country has 3 rows for 3 location types)
What I am trying to do is to see if there is a way to fill the empty cells in the "Country" column, with the appropriate Country name, which would give something like this:
| Country | Type of location |
|--------- |------------------ |
| A | 1 |
| A | 2 |
| A | 3 |
| B | 1 |
| B | 2 |
| B | 3 |
| C | 1 |
| C | 2 |
| C | 3 |
For now I thought about using a series of if/else if conditions, but as there are 100+ countries this doesn't seem like the right solution.
Is there any way to do this more efficiently?