VLOOKUP OR INDEXMATCH ALTERNATIVE TO FIND VALUE BASED ON CELL VALUE

[Vestments7]

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?

[serpiva64]

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!

[Vestments7]

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.

[serpiva64]

Hi,

Here is the project in excel.

the commands between BI and excel a quite the same

[Vestments7]

Thank you for your message. You are using Query, but mine is loaded as Data Model and use PowerPivot. How to do that using Data Model? There is no much feature to operate and find the solution.