Create custom column with values in same row as other column based on criteria

[tc5pt]

Hello,

I have a single column as follows (numbers simplified):

100,000,000

200,000

200,500

201,000

201,500

101,000,000

202,000

202,500

203,000

I would like to create a new column, that duplicates the 9 figure number in the same row that they appear in the above column. So it would look like:

Column A           Column B

100,000,000       100,000,000

200,000              null

200,500              null

201,000              null

201,500              null

101,000,000       101,000,000

202,000              null

202,500              null

203,000              null

These numbers are representative and actually represent codes (these are not actually used as numerical values). The 6 figure numbers are essentially subcodes of the 9 figure number. The idea is the then to Fill Down these larger figures. Maybe there is a way to get to that point directly? I figure there is some kind of way to put together a function that goes something like, "If Column A is greater than/contains X, then column B equals Y (in the same row), otherwise, null".

Thank you in advance

[Kjtakke · Accepted solution]

This is a basic custom function that does the trick:
Column A must be 'text' not 'number' or 'Int64'

(ColumnA as text) =>
let
Evaluation = if Number.FromText(ColumnA) < 100000000 then "null" else ColumnA
in
Evaluation

--------------------------------------------------------------------------------------
It Column A starts with a 0 then use:
(ColumnA as text) =>
let
Evaluation = if Text.Length(ColumnA) &lt; 9 then "null" else ColumnA
in
Evaluation
--------------------------------------------------------------------------------------
Alternatively, use a custom column:
= Table.AddColumn(#"Changed Type", "Column B", each if Number.FromText([Column A]) < 100000000 then "null" else [Column A])

or
= Table.AddColumn(#"Changed Type", "Column B", each if Text.Length([Column A]) < 9 then "null" else [Column A])

[tc5pt]

@Kjtakke 

Thank you for your response. This is exactly what I asked for.

Is there anyway to make it return null (ie. no value) as opposed to "null" so I can use Fill Down?

[Kjtakke]

Yes,

Replace “null” in the formula to any value you wish i.e “no value”

This only works if the column is set to text.