Identify specific values in a column and repeat for other rows

[Jon von der Heyden]

Hi All

I have a column of mixed information. Every now and then the value refers to a cost centre which is identified as beginning with "cc-". The other values could be other master data fields or even transactional values.

I wish to create a new column called "cost centre" which will always reflect the previously found cost centre. The table below shows the desired output.

Excel 2013

	A	B
1	Original	New Col Results
2	Values	Cost Centre
3	cc-100	cc-100
4	gasdj	cc-100
5	jbh s	cc-100
6	4598	cc-100
7	cc-101	cc-101
8	snkhj	cc-101
9	109	cc-101
10	bjhgsa	cc-101
11	cc-102	cc-102
12	kjhnd	cc-102
13	jkhsd	cc-102

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

So far my attempts have lead me to this, although I seem unable to reference the identified cost centre from the same column, but from the record above. I get an error produced by the Else statement in CC1; "The field 'Cost Centre1' of the record wasn't found.".

let
    Source = Excel.CurrentWorkbook(){[Name="tblCC"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    CC1 = Table.AddColumn(#"Added Index", "Cost Centre1", each if Text.Start(Text.From([Label]),3)="cc-" then Text.From([Label]) else #"Added Index"{[Index]-1}[Cost Centre1])
in
    CC1

...where Label is the column of mixed values


Hope this makes sense.

Regards
Jon

 

[ImkeF]

Hi Jon
You cannot reference a column within a column you're just creating in Power Query without using a recursive function. But this would be slow so I don't recommend it.
Instead, you can do the following: Add a custom column with your existing check on "cc-". If true return this value and if false return null instead. Then you check that column and fill down values.

Kind regards, Imke

 

[Jon von der Heyden]

Super! Big thanks, Imke. I actually did not know of Fill Down.