CODEWITHSUNDEEP
excelpowerbipowerquery
Charlene
Charlene

Reputation: 1

Translating excel formula into power query language

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.

Qualifications column image

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

Upvotes: 0

Views: 157

Answers (1)

horseyride
horseyride

Reputation: 21298

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}\\)","")

Upvotes: 1

Related Questions