cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

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".

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

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

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])
3 REPLIES 3
Frequent Visitor

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

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])
Frequent Visitor

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

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?

Frequent Visitor

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

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.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,171)