Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @Anonymous
Please see the attached file with a solution.
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.
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.
Hey,
Thanks a lot! Your explanation clarfied quite a bit to me. I was not that far after all. This kind of guidance actually helps to learn yourself on how to do it.
Hi @Anonymous
Please see the attached file with a solution.
Hey,
The attached file was exactly what I was looking for - thanks! Used this and the second reply to get the desired outcome. This was the straight answer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |