cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
featleyd Regular Visitor
Regular Visitor

Replicating Regex expressions in Power BI Desktop

Hello all

 

I am using the Google Analytics connector to bring through an entire websites data (thousands of different addresses), and I am trying to replicate a report I run in Google Analytics.

 

This report uses Regex expressions to include URL's with certain keywords in the URL (currently 5) , but exclude certain strings contained within the URL (currently 17 individual strings). I use the report to provide pageviews amongst other information.

I am trying to replicate the Regex using filters in Power BI desktop, and it simply isn't up to the task, the basic filter can't keep up with the vast number of addresses and crashes out, and the Advanced filter isn't providing enough spaces to enter all of the inclusions and exclusions, as well as returning blank when I fill all of the allowed spaces.

 

I'm wondering if there is anyone who can offer any assistance in order to help me achieve my objective.

 

Thanks in advance

 

Daniel

1 ACCEPTED SOLUTION

Accepted Solutions
AnthonyTilley Established Member
Established Member

Re: Replicating Regex expressions in Power BI Desktop

HI not sure if this is what you are after but it works for me 

 

i would create two tables 1 for your include strings and one for your exclude strings (do not put in same table must be 2 diffrent tables)

 

Then use the formula below.

 

Simply put, Var incl will look thorugh your include table and if it finds any refrences in the string it will give it a value. if the total value is greater than 0 then a refrence was found and the value is set as true. if none of the strings in your include table are found then all values will be zero and this will be given the value of false. it will then do the same for the exclude list, only i this case if it finds no refrence then it is true and if it finds a refrence it is false. the two valuse are then compared and if both are true then a true is given if one is false then a false is given.

Column = 
VAR incl = if(
      SUMX(INCLUDE,
           FIND(
                UPPER(INCLUDE[INCLUDE]),
                UPPER('Table'[Column1]),1,0)
          ) > 0, TRUE,FALSE)
VAR Exc = if(
      SUMX(EXCL,
           FIND(
                UPPER(EXCL[EXCLUDE]),
                UPPER('Table'[Column1]),1,0)
          ) > 0, FALSE,TRUE) 

 var ret = if(AND(incl,Exc),TRUE,FALSE)
 RETURN ret         

you should then be able to use this colunm as a filter and only include your true values.

 

in this example your URL string would have to appear in the include list but not apear in the exclude list anything else will be given the value false and filtered out.

 

this formula also means that you can add new values to the list or remove values form the lists and the folrmula will reflect this without needing to make changes to your filters.

 

hope this helps 

 

below is an example with a few sets to show its working. table 1 is the exclude list, table 2 the include list and table three some sample data

 

you can see that test is in the include so rows 1 is true as it is in the include list but not in the exclude list this is the same for row 2 that has the word KEEP. further in the list we have TESTME and KEEP5 which are also true becuse they contain the test and keep strings. however the last entry is KEEPDONOTUSE so although it contains the String KEEP it also contains DONOTUSE witch is in the exclude list so this is showing as false.

the STRING NOTINANYLIST is given faluse becuase it is not in the include list 

 

 

EXCLUDE

DONOTUSE
IGNORE

 

INCLUDE

KEEP
INCLUDE
TEST

 

Column1Column

TESTTRUE
KEEPTRUE
IGNOREFALSE
DONOTUSEFALSE
INCLUDETRUE
KEEP55TRUE
KEEPTHISTRUE
IGNORETHISFALSE
DONOTUSEFALSE
TESTMETRUE
NOREFFALSE
NOTINANYLISTFALSE
KEEPDONOTUSEFALSE

View solution in original post

3 REPLIES 3
AnthonyTilley Established Member
Established Member

Re: Replicating Regex expressions in Power BI Desktop

HI not sure if this is what you are after but it works for me 

 

i would create two tables 1 for your include strings and one for your exclude strings (do not put in same table must be 2 diffrent tables)

 

Then use the formula below.

 

Simply put, Var incl will look thorugh your include table and if it finds any refrences in the string it will give it a value. if the total value is greater than 0 then a refrence was found and the value is set as true. if none of the strings in your include table are found then all values will be zero and this will be given the value of false. it will then do the same for the exclude list, only i this case if it finds no refrence then it is true and if it finds a refrence it is false. the two valuse are then compared and if both are true then a true is given if one is false then a false is given.

Column = 
VAR incl = if(
      SUMX(INCLUDE,
           FIND(
                UPPER(INCLUDE[INCLUDE]),
                UPPER('Table'[Column1]),1,0)
          ) > 0, TRUE,FALSE)
VAR Exc = if(
      SUMX(EXCL,
           FIND(
                UPPER(EXCL[EXCLUDE]),
                UPPER('Table'[Column1]),1,0)
          ) > 0, FALSE,TRUE) 

 var ret = if(AND(incl,Exc),TRUE,FALSE)
 RETURN ret         

you should then be able to use this colunm as a filter and only include your true values.

 

in this example your URL string would have to appear in the include list but not apear in the exclude list anything else will be given the value false and filtered out.

 

this formula also means that you can add new values to the list or remove values form the lists and the folrmula will reflect this without needing to make changes to your filters.

 

hope this helps 

 

below is an example with a few sets to show its working. table 1 is the exclude list, table 2 the include list and table three some sample data

 

you can see that test is in the include so rows 1 is true as it is in the include list but not in the exclude list this is the same for row 2 that has the word KEEP. further in the list we have TESTME and KEEP5 which are also true becuse they contain the test and keep strings. however the last entry is KEEPDONOTUSE so although it contains the String KEEP it also contains DONOTUSE witch is in the exclude list so this is showing as false.

the STRING NOTINANYLIST is given faluse becuase it is not in the include list 

 

 

EXCLUDE

DONOTUSE
IGNORE

 

INCLUDE

KEEP
INCLUDE
TEST

 

Column1Column

TESTTRUE
KEEPTRUE
IGNOREFALSE
DONOTUSEFALSE
INCLUDETRUE
KEEP55TRUE
KEEPTHISTRUE
IGNORETHISFALSE
DONOTUSEFALSE
TESTMETRUE
NOREFFALSE
NOTINANYLISTFALSE
KEEPDONOTUSEFALSE

View solution in original post

featleyd Regular Visitor
Regular Visitor

Re: Replicating Regex expressions in Power BI Desktop

@AnthonyTilley 

That is an unbelievably brilliant piece of DAX. It got me to the figure I was looking for first time!

If I could give more than 1 kudos to this I would.

 

Thank you so much for your help!

 

Daniel

AnthonyTilley Established Member
Established Member

Re: Replicating Regex expressions in Power BI Desktop

Thanks, its always nice to know the effort is appreciated

 

Glad i was able to help.

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,940)