Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I am new to M code. I am trying to create a column A based on data in Column B. Column A objective is to get column B data if Column B contains text. For e.g. if column B contains BNP, then, Column A should have full text of cells containing BNP in column B.
can anybody help me.
Solved! Go to Solution.
Hello @robinshalabh
the problem I see with the solution from @Payeras_BI is if you have a column that is formated as text not as any. This would mean, everey value is text and so you will get not the desired result. When you use this formula, it should always work
let
Check = try Number.From([ColumnB])
in
if Check[HasError] = true then [ColumnB] else null
Here the complete code example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvILUIrViVYyNDIG045gEi4KJk3BpJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnB", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "ColumnA", each let
Check = try Number.From([ColumnB])
in
if Check[HasError] = true then [ColumnB] else null)
in
#"Added Custom"
transform this
into this
Hello @robinshalabh
the problem I see with the solution from @Payeras_BI is if you have a column that is formated as text not as any. This would mean, everey value is text and so you will get not the desired result. When you use this formula, it should always work
let
Check = try Number.From([ColumnB])
in
if Check[HasError] = true then [ColumnB] else null
Here the complete code example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvILUIrViVYyNDIG045gEi4KJk3BpJlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnB", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "ColumnA", each let
Check = try Number.From([ColumnB])
in
if Check[HasError] = true then [ColumnB] else null)
in
#"Added Custom"
transform this
into this
Hi @robinshalabh ,
Add a Custom Column and type in the following code:
if Value.Is([Column B],type number) then null else [Column B])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.