Forum Discussion

RajivRavichand's avatar
RajivRavichand
Frequent Visitor
3 years ago
Solved

Error Extraxt after delimiter if contains _ or return same value

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
[email protected] | BHAVANI AYASAMY
Reconciler Due Date=10/30/2022
Reconciler status=û
Late reconciliation?=
[email protected] | 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=

Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    3 years ago

    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)

     

     

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    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)

     

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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.