If text contains “-”, then extract text after “-“ delimiter.

[PowerBIUser9901]

Hi, I need guidance in how can I create a new step in Power Query (not a calculated column) that if text contains “-“ then extract text after “-“ delimiter. I'm having trouble in how I reference the column and previous step.

Screenshot below:

I included the PBIX file if that helps. Link Below:

https://1drv.ms/u/s!AltxA49hBwkYgetlzTpbpo2qoip6hw?e=zNtDmf

I appreciate any help you can provide. Thank you!

[RajivRavichand]

Hi Im also facing same kind of secnario, 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=

[mahoneypat]

Add a custom column, with this formula.

if Text.Contains([#"Person/Device"], "-") then Text.AfterDelimiter([#"Person/Device"],"-") else [#"Person/Device"]

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[PowerBIUser9901]

Thanks @mahoneypat I appreciate it. Is there a way to use this code as a next step in my query and edit the existing column rather than create a custom column?

[mahoneypat · Accepted solution]

Yes.  I actually started that way but thought it would be harder to explain how to implement it.  But since you asked ...

= Table.TransformColumns(#"Added Custom", {{"Person/Device", each if Text.Contains(_, "-") then Text.AfterDelimiter(_,"-") else _, type text}})

Right click on the desired previous step in the Applied Steps window and "Insert Step After".  Paste this into the formula bar, and update #"Added Custom" with the name of the actual previous step.

FYI you could also use the previous approach, delete the original column, and rename the added one to the same name.  But I also like to transform columns directly too when I can.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[PowerBIUser9901]

Thank you @mahoneypat . I really do appreciate your help and clear instructions.