Forum Discussion
Fill null cells with selected data
Hi,
I have to set a Class for each User & Channel in Power Query. I have created a Column that assign a class for the first Channel in pipeline (1487). Now, I'd like to set the same class to other Channels for the same user. I have tryed grouping the data, it was close, but I didn't managed to get the correct answer. Fill also didn't worked because the data in my table is not consecutive and we have no option to sort the data by 2 tables simultaneously .
Here you have a data sample:
| user_id | status | channel | Custom |
| 64248 | COMPLETE | 1488 | null |
| 64248 | COMPLETE | 1487 | Class2 |
| 64249 | COMPLETE | 1488 | null |
| 64249 | COMPLETE | 1487 | Class1 |
| 64250 | COMPLETE | 1488 | null |
| 64250 | COMPLETE | 1487 | Class3 |
| 64250 | COMPLETE | 1491 | null |
| 64253 | COMPLETE | 1488 | null |
| 64253 | COMPLETE | 1487 | Class4 |
| 64253 | COMPLETE | 1491 | null |
| 64255 | COMPLETE | 1488 | null |
| 64255 | COMPLETE | 1487 | Class2 |
| 64256 | COMPLETE | 1488 | null |
| 64256 | COMPLETE | 1487 | Class5 |
| 64257 | COMPLETE | 1488 | null |
| 64257 | COMPLETE | 1487 | Class2 |
| 64257 | COMPLETE | 1491 | null |
| 64258 | COMPLETE | 1488 | null |
| 64258 | COMPLETE | 1487 | Class1 |
| 64258 | COMPLETE | 1491 | null |
I'd like to have the nulls populated with the data which is shown for Channel 1487 for each user.
| user_id | status | channel | Custom |
| 64248 | COMPLETE | 1488 | Class2 |
| 64248 | COMPLETE | 1487 | Class2 |
| 64249 | COMPLETE | 1488 | Class1 |
| 64249 | COMPLETE | 1487 | Class1 |
| 64250 | COMPLETE | 1488 | Class3 |
| 64250 | COMPLETE | 1487 | Class3 |
| 64250 | COMPLETE | 1491 | Class3 |
| 64253 | COMPLETE | 1488 | Class4 |
| 64253 | COMPLETE | 1487 | Class4 |
| 64253 | COMPLETE | 1491 | Class4 |
| 64255 | COMPLETE | 1488 | Class2 |
| 64255 | COMPLETE | 1487 | Class2 |
| 64256 | COMPLETE | 1488 | Class5 |
| 64256 | COMPLETE | 1487 | Class5 |
| 64257 | COMPLETE | 1488 | Class2 |
| 64257 | COMPLETE | 1487 | Class2 |
| 64257 | COMPLETE | 1491 | Class2 |
| 64258 | COMPLETE | 1488 | Class1 |
| 64258 | COMPLETE | 1487 | Class1 |
| 64258 | COMPLETE | 1491 | Class1 |
Any suggestions will be much appreciated.
- Sorry, I don't understand why Fill Down won't work? You just need to sort the data by user_id and channel. This post is for Excel, but the same thing can be done in Power Query Editor in Power BI to sort by multiple columns and then you can use the Transform Column > Fill Down option
https://support.microsoft.com/en-us/office/sort-a-table-power-query-baf200d5-1097-49cf-80b3-f1ce07b5f114
3 Replies
- amitchandakSuper User
Anonymous , you have an option to fill dow in the power query
https://www.excelcampus.com/powerquery/fill-down-blank-null-cells-power-query/
https://www.youtube.com/watch?v=vQIDJVkFW8o
First row will need some different logic
- AllisonKennedyCommunity ChampionSorry, I don't understand why Fill Down won't work? You just need to sort the data by user_id and channel. This post is for Excel, but the same thing can be done in Power Query Editor in Power BI to sort by multiple columns and then you can use the Transform Column > Fill Down option
https://support.microsoft.com/en-us/office/sort-a-table-power-query-baf200d5-1097-49cf-80b3-f1ce07b5f114- AnonymousNot applicable
After some fine tunings in my datasource, I've achieved the result with the Fill Option. It wasn't working properly in the first instance because of some additional data that I had. Thanks