cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tc5pt Frequent Visitor
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".

 

Thank you in advance

1 ACCEPTED SOLUTION

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

View solution in original post

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

View solution in original post

tc5pt Frequent Visitor
Frequent Visitor

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

@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 Frequent Visitor
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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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