Forum Discussion
How to fix table data to fill null values properly with Power Query
Hey community,
Yet again I'm unable to find resolution for a table manipulation that I'm working with.
I've been trying outer join with 2 different tables (1 with correct values, and second one that needs to be fixed) However, Ive been unable to figure how to solve this properly.
The use case is described below and after that there are some notes what I have tried.
After few transformations my table looks like this (snippet of the beginning):
| Name | Value |
| 0 | 38 |
| 1 | 111 |
| 3 | 149 |
| 5 | 799 |
| 6 | 4636 |
| 7 | 5435 |
The table continues as such, total amount of rows is a bit over 3000.
However the issue in this is that the data structure is strictly based on "index" (Name) here and it's missing the null values completely which I need in order to post process the data in a more profound manner.
The target outcome should be following from the source table
| Name | Value |
| 0 | 38 |
| 1 | 111 |
| 2 | 0 |
| 3 | 149 |
| 4 | 0 |
| 5 | 799 |
| 6 | 4636 |
| 7 | 5435 |
So based on the Name column, whenever its "skipping" a number I need to add a row with value being 0.
What have I tried?
I tried to create another table which has the 3000 rows as index values and then outer join to this source table I got following result...
| Index | Name | Value |
| 0 | 0 | 38 |
| 1 | 1 | 111 |
| 2 | 3 | 149 |
| 3 | 5 | 799 |
| 4 | 6 | 4636 |
| 5 | 7 | 5435 |
| ... | ... | ... |
| 2630 | null | null |
Which is closer to the desired result, however what I struggle here is that how can I make the table to actually follow the (add missing spots) the index column?
i.e so it would be something like this
| Index | Name | Value |
| 0 | 0 | 38 |
| 1 | 1 | 111 |
| 2 | null | null |
| 3 | 3 | 149 |
| 4 | null | null |
| 5 | 5 | 799 |
| ... | ... | ... |
I have a feeling that I'm trying to over engineer this and hitting a wall here.
Is there anyone in the community that could assist with the above case?
It's the correct strategy but the join step is incorrect. Either the data types are incompatible OR the tables were in the wrong order OR the wrong type of join is being used.
It should be a 'Merge as new' with Left Outer join kind , with the number table at the top (join on Index and Name and they should be same data type). Then expand the table column.
4 Replies
It's the correct strategy but the join step is incorrect. Either the data types are incompatible OR the tables were in the wrong order OR the wrong type of join is being used.
It should be a 'Merge as new' with Left Outer join kind , with the number table at the top (join on Index and Name and they should be same data type). Then expand the table column.