Notifications
Clear all
Power Query
3
Posts
2
Users
0
Reactions
390
Views
Topic starter
I need to use index and offset to get a parent value in the same value as a child. Or if you have another suggestion. I'm new to power query and this forum has been very helpful.
So below is an example I need.
How could I do the same thing in power query?
| NAME | Offset/Index (row) |
| PARENT 1 | 0 |
| CHILD 1 | 1 |
| PARENT 2 | 0 |
| CHILD 1 | 1 |
| CHILD 2 | 2 |
In excel, I use a the offset formula to get the parent to the child row.
=OFFSET([@NAME],-[@[Offset/Index (row)]],0)
Results below
| CHILD | PARENT |
| CHILD 1 | PARENT 1 |
| CHILD 1 | PARENT 2 |
| CHILD 2 | PARENT 2 |
Posted : 30/01/2020 2:08 pm
In Power Query, add a new column with this formula:
if Text.Contains([Name],"PARENT") then [Name] else null
Then select the new column and use the fill down from the transform tab, filter out parents from Name column.
Posted : 30/01/2020 10:23 pm
Topic starter
Thank you so much! That was exactly what I needed and a lot easier than what I wanted to do!
Posted : 31/01/2020 11:10 am
