Error Extraxt after delimiter if contains _ or return same value

[RajivRavichand]

Hi Im facing issue when using the Extraxt after delimiter, I used below expression but facing issue. Can you please check is there any issue in below DAX.

= Table.AddColumn(#"Duplicated Column", "Region", each if Text.Contains(_, "-") then Text.AfterDelimiter([#"Region/Division"], "_") else _, type text)

issue:  
Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=
Entity=90000 | Hong Kong Div 380
Region/Division=APS_AME_ASIA
Reconciliation Object=90000_45000
Description=Called up Share Capital
Risk Rating=Medium Risk
Currency=HKD
General Ledger Data=-5000000
Explanation Items Amount=-5000000
Reconciling Items Amount=0
Unexplained difference=0
Unexplained percentage=0
Reconciliation Status=To reconcile
Reconciler=BHAVANI.AYASAMY@AGGREKO.COM | BHAVANI AYASAMY
Reconciler Due Date=10/30/2022
Reconciler status=û
Late reconciliation?=
Reviewer=WEI.DONG@AGGREKO.COM | WEI DONG
Reviewer status=û
1st Approver=
1st Approver status=
2nd Approver=
2nd Approver status=
Scope=In Scope
Tower=FAPE
Comments if not in scope=0
Target completion Date=10/11/2022
Status=Overdue
Reconciler status_1=N
Reviewer status_2=N
1st Approver status_3=
2nd Approver status_4=
ReconcilerName= BHAVANI AYASAMY
Reviewer Name= WEI DONG
First Approver Name=
Second Approver Name=

[RajivRavichand]

Thanks. Your expression is woeked fine for me.

[v-yanjiang-msft]

Hi @RajivRavichand ,

According to your description, here's my solution.

= Table.AddColumn(#"Duplicated Column", "Region", each if Text.Contains([#"Region/Division"], "-") then Text.AfterDelimiter([#"Region/Division"], "-") else [#"Region/Division"], type text)

Result:

Here's the whole M syntax, you can copy-paste in a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctR1UorViVZydgFTrrpuSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Region/Division" = _t]),
    #"Duplicated Column" = Table.TransformColumnTypes(Source,{{"Region/Division", type text}}),
    #"New"= Table.AddColumn(#"Duplicated Column", "Region", each if Text.Contains([#"Region/Division"], "-") then Text.AfterDelimiter([#"Region/Division"], "-") else [#"Region/Division"], type text)
in
    #"New"

I also attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF · Accepted solution]

Hi @RajivRavichand ,
the reason for this error message lies in the underscore. This represents a record in that environment.
Did you mean to put a reference to a column there instead (like you did in "Region/Division"):

= Table.AddColumn(#"Duplicated Column", "Region", each if Text.Contains(_, "-") then Text.AfterDelimiter([#"Region/Division"], "_") else _, type text)

replace by:

= Table.AddColumn(#"Duplicated Column", "Region", each if Text.Contains([YourColumnName], "-") then Text.AfterDelimiter([#"Region/Division"], "_") else _, type text)