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