Replicating Regex expressions in Power BI Desktop

[Anonymous]

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

[AnthonyTilley · Accepted solution]

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

TEST	TRUE
KEEP	TRUE
IGNORE	FALSE
DONOTUSE	FALSE
INCLUDE	TRUE
KEEP55	TRUE
KEEPTHIS	TRUE
IGNORETHIS	FALSE
DONOTUSE	FALSE
TESTME	TRUE
NOREF	FALSE
NOTINANYLIST	FALSE
KEEPDONOTUSE	FALSE

[sergiofco]

Hi, Anthony, thank you so much for this! You really deserve this Super User alias!

[Anonymous]

@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]

Thanks, its always nice to know the effort is appreciated

Glad i was able to help.