Become the best user of Power BI : Regex
By the time you finish this tutorial, you will become one of the best POWER BI Wizards. You'll find yourself becoming quite the adept POWER BI RegEx user. Not only will you handle RegEx tasks with ease, but you'll also seamlessly integrate JavaScript into your Power BI workflow. Your efficiency will get a boost thanks to smart AI tools you will have picked up along the way. Companies might just appreciate having someone as skilful and versatile as you on their team :D
Download the excel file from this link to follow through with the tutorial.
We have this Payslip Table in the Sheet1
What do we want to do in Power BI? We want to use Power Query and RegEx for extracting the Slip number code from the string/ sentences only.
Step 1:
Fire up the Power BI Desktop and load up the excel file in the Power Query Editor.
Step 2:
Go to Home> New Sources > Blank Query
Now rename the Query to "Re". Stay on this page. DO NOT CHNAGE ANYTHING NOW. We will create an M Query Function on this page. What does a function do? Maybe I will create more tutorials on that later. Briefly, just like any other programming languages, a function takes parameters and performs the specified operations on the parameters. In this case, We will pass on two parameters, one for Regular Expression (regex), the other for the column string, from which you want to extract the value from (str).
Now you might ask, what is a Regular Expression, worry not, I got you covered, remember I am the best?
Click here if you like videos
Click here if you like to read
How to build these regular expressions? In the olden days, our grandpas used tools like RegExr: Learn, Build, & Test RegEx; Indeed, Chatgpt can answer.
Chatgpt prompt engineering: When you come to Sihan, you will learn so many things at ones. Now I will teach you Chatgpt prompt engineering here? Yes, Free. I know datacamp charges for it. Customise the following prompt to meet you desired needs and chatgpt will create the regex for you.
Given the following sample text,
I need to extract the phone numbers.
The phone numbers are in the format XXX-XXX-XXXX.
Please create a regex pattern that accurately
captures these phone numbers. Note that the text
structure may vary, but the phone numbers s
hould be extracted consistently.
Text sample:
"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Contact us at: 123-456-7890 or visit our office at 456-789-0123."
Once you have the regex from Chatgpt, save it somewhere. For this tutorial, regex going to be: /SLIP\d+/ because we are matching the texts that starts with SLIP and a lot of digits following it.
Now back to Power Query in that re blank query you created.
A screen will pop up, this is called M Query Editor. Remove everything and paste the following code on the window.
(regex as text , str as text)=>
let
html = "<script>var regex = " & regex & ";var str = """ & str & """;var res = str.match(regex);document.write(res)</script>"
in
html
Code Breakdown:
(regex as text , str as text)=>
Declares two function parameters. One takes in the regex pattern. In our cases- /SLIP\d+/, the other takes in the description columns from our excel file.
"<script>var regex = " & regex & ";var str = """ & str & """;var res = str.match(regex);document.write(res)</script>"
This line is wrapping a JavaScript code into a string.
"<script>var regex = " --- declaring variable inside the quotations marks
& regex & --- this part is outside the quotation as it fetches the variable from the parameter
";var str = " --- inside the quotations marks
"" & str & "" --- double quotation is used to indicate that str parameter values will sit inside a singl equatation in the result.
";var res = str.match(regex);document.write(res)</script>" --- rest of the code to put the function operation result in a res varible.
var res = str.match(regex);
This line uses the match method on the string (str) with the provided regular expression (regex). It attempts to find matches in the string based on the specified regular expression. The result is stored in the variable res.
document.write(res);
This line writes the result (res) to the document. If there are matches, it will display the matched substrings; otherwise, it may display null. The document.write method is a simple way to output content to the document.
WHY doing all these? Because, when we execute this function, we want to see the result look like the image below:
Can you see from the picture why we used a "" & str & ""?
Step 4:
We created the function in the last step. Now all we have to do is invoke the function.
Go to the main Query where you loaded your excel. Create a custom column. The pass in the parameters for re() function that we just created.
Click OK. Now you have the Dynamic html code ready to execute. All these gymnastics we did was to create a dynamic HTML code. Now, we need to run this HMTL code using Web.Page function of Power Query.
Step 5:
Create another customer column. Call it, result.
After you click OK, you will see something like the following image:
I am sure from here you can pick up what you have to do. You can expand the column manually or you can edit the M Query. If you want to edit the M Query, replace the following code-
= Table.AddColumn(#"Added Custom1", "Results", each Web.Page([html]))
with-
= Table.AddColumn(#"Added Custom1", "Results", each Web.Page([html])[Data]{0}[Children]{0}[Children]{1}[Text]{0})
Done. Now you are the BEST POWER BI RegEx user. Not only that, you can write JavaScript that too on Power BI. You have learnt to make yourself much efficient with the help of AI tools. Any company should strive to have people like you.