Forum Discussion
VLOOKUP OR INDEXMATCH ALTERNATIVE TO FIND VALUE BASED ON CELL VALUE
This is what I have:
| ID | Name |
| 1 | |
| 1 | |
| 1 | A |
| 2 | |
| 2 | B |
| 2 | |
| 3 | |
| 3 | |
| 3 | |
| 3 | C |
And this is what I need:
| ID | Name |
| 1 | A |
| 1 | |
| 1 | |
| 2 | B |
| 2 | |
| 2 | |
| 3 | C |
| 3 | |
| 3 | |
| 3 |
Which formula is the best in the PowerQuery M environment if it's same table? I have tried FIRSTNONBLANK function and I got an error. Then I tried LOOKUPVALUE and also got an error. I am confused about what is wrong? How to sort/lookup properly based on ID value?
4 Replies
Hi,
you can obtain
by grouping
then adding a nested index column
expanding
and finally adding a conditional column
You can see the steps in the attached file
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
Cool and thanks for your answer. But this is not I am looking for as this in DAX environment. But how to do that using PowerPivot (PowerQuery M) that is built in Excel? There is no Group by feature and also no Table.AddIndexColumn formula.
Hi,
Here is the project in excel.
the commands between BI and excel a quite the same
