Forum Discussion
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
- 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) < 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
- 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) < 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])
