Translating excel formula into power query language

Question

Could someone please assist with helping me create a formula using Power Query that performs the same as the following Excel formula?

=iferror(iferror(if(value(left([Qualifications],1))>0,right([Qualifications],len([Qualifications])-7),""),left([Qualifications],find("/",[Qualifications])-4)),[Qualifications])

Essentially I am trying to strip out expiration dates from a column which contains the qualification title and expiration date (if any) at the end of the title, ideally to just show the qualification title. As some of the qualifications use () in the title, I need to search for the presence of / in the title and remove characters around these.

As I'm new to using Power Query, it's proving a little time consuming trying to learn and understand the different language to Excel!

EDIT - This is the Token Eof error I receive when pasting the provided code:

enter image description here

EDIT 2 - This is the error that is showing in multiple rows, including the row with the date at the end

enter image description here

You don't show what you want for output, but if your output would be all those lines with any dates removed, then I'd probably just use a regular expression to replace the date with nothing — Ron Rosenfeld
– Ron Rosenfeld, Commented Feb 11, 2022 at 1:44

horseyride · Accepted Answer · 2022-02-16 15:10:40Z

1

A powerquery solution .. Assuming you want to remove (##/##/####) then

Paste below into a blank query in home ... advanced editor ... name it RegExReplace and then file .. close and load

/*
https://www.mathscinotes.com/2020/02/power-query-regular-expression-hack/
Mark Biegert
Modified version of regex routine documented by Imke Feldman at
https://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-in-excel-with-java-script/
Input Parameter:
x : String to be searched
y : Search regex
z : Replacement regex
*/
(x,y,z)=>
let Source = Web.Page(
"<script>var x="&"'"&x&"'"&";var z="&"'"&z&
"'"&";var y=new RegExp('"&y&"','g');
var b=x.replace(y,z);document.write(b);</script>")
[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in Source

Then in your data to clean, Add column .. custom column .. with formula

= RegExReplace([YourColumnName],"\\(\\d{2}\/\\d{2}\/\\d{4}\\)","")

edited Feb 16, 2022 at 15:10

answered Feb 11, 2022 at 13:43

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Charlene Over a year ago

When I paste the formula into advanced editor, I receive a "Expression.SyntaxError: Token Eof expected" error - could you please let me know how I would fix this?

horseyride Over a year ago

My code works as listed. Edit your question to post a copy of your code for us to look at. Did you use the name of your column instead of YourColumnName? Did you name the function RegExReplace and use that same spelling and capitalization when you referred to it in your custom column formula?

Charlene Over a year ago

Please see the edit I have included regarding the Token Eof error - this is occurring when pasting the provided code, and I am not able to progress to the second step of creating the custom column. Could you please assist?

horseyride Over a year ago

Odd. What happens if you remove all text before (x

Charlene Over a year ago

The text above is the API collection of data and some other data cleaning I have completed. Without this, I would assume the code you have provided won't have any data to reference? Prior to adding the code provided, there are no errors etc. Is there a way to include the code you have provided and still keep the API data?

|

Collectives™ on Stack Overflow

Translating excel formula into power query language

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related