CODEWITHSUNDEEP
excelpowerquery
slepax
slepax

Reputation: 83

Reference previous row in Power Query

I have the following dataset that I am reading into Power Query:

Value
36628848
36659230
36709969
O-386082
O-390825
O-390827
37419002
37689374
O-390274
37919295
38012195
O-390586
O-380013
38104658
38287760

The values starting with O- belong to the value in the row above it, so for example O-386082, O-390825, O-390827 all belong to 36709969.

I would like to create another column which has the parent of each 'O-' entry. So the output should look like:

Value       Parent
36628848    36628848
36659230    36659230
36709969    36709969
O-386082    36709969
O-390825    36709969
O-390827    36709969
37419002    37419002
37689374    37689374
O-390274    37689374
37919295    37919295
38012195    38012195
O-390586    38012195
O-380013    38012195
38104658    38104658
38287760    38287760

Doing this in Excel is easy, because I can do: Parent = Value if Value does not start with 'O-' else Parent @ row-1

I have tried using Table.Range but I can not run it on the same table. Is there a way to this in Power Query?

Thanks!

Upvotes: 0

Views: 413

Answers (1)

horseyride
horseyride

Reputation: 21413

In powerquery

click Value column ... transform ... data type ... text

add column ... custom column ... named Parent with formula

= if Text.Start([Value],2)="O-" then null else [Value]

Right-click Parent column .. fill ... down

File ... Close and Load

Sample full code, if data was coming from Table1

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Parent", each if Text.Start([Value],2)="O-" then null else [Value]),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Parent"})
in  #"Filled Down"

Upvotes: 1

Related Questions