Pageviews per product number

[mdiephuis]

Good morning everybody,

I have a table with a list of unique product numbers, so only one of each product number is mentioned in this list.

Besides that a have a list with all URLs (page) of my website with in another column the amount of pageviews per URL. The product numbers are part of the URL-structure, but at random places in the URL.

              

Now, I want to know how many pageviews there are per product number.

Can anyone help me with this?

Thank you so much!

Best regards,

Michael

[v-stephen-msft]

Hi @mdiephuis ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[mdiephuis]

Hi!

No, they are not always proceeded by this.

Here underneath some examples of a few product pages on my website, where I highlighted in red what is the product number:

https://www.horloge.nl/g-shock-g-steel-solar-herenhorloge-met-smartphone-link-gst-b300s-1aer.htm

https://www.horloge.nl/casio-gents-classic-zwart-quartz-herenhorloge-mw-240-1evef.htm

https://www.horloge.nl/timex-camper-sportief-zwart-horloge-op-olijfgroene-nylon-band-t2n363.htm

https://www.horloge.nl/citizen-vintage-retro-stijl-eco-drive-chronograaf-ca7061-26x.htm

So; They are always at the back of the URL, before ".htm", but the product numbers have different structures.

Thank you!

[bcdobbs]

How big the data set? Sure there is a more efficient way of doing it but in power query you could cross join all the urls with all of the product codes (add a custom column with expression equal to product code). Then add another custom column and use text.contains to check if product code is in the url. Then filter out rows without matches. Sounds ugly just writing it!

[mdiephuis]

The product list contains 400.000+ rows.

The list with URLs contains 450.000+ rows.

[v-stephen-msft · Accepted solution]

Hi @mdiephuis ,

Sample data

Product table

URL table

You can try this calculated column.

Pageviews = var _table= FILTER(ADDCOLUMNS(CROSSJOIN('Product','URL'),"a",CONTAINSSTRING([Words],[Keyword])),[a]=TRUE())
return MAXX(FILTER(_table,[Words]=EARLIER('Product'[Words])),[Value])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mdiephuis]

Hi Stephen,

Sorry for my late reply, I have been sick with corona and after that a harsh recovery time and then getting back on track with work.

Thank you so much for your offered solution.

Although I am convinced it works, it doesn't work for my dataset;

I have around 435.000 individual products and I sell them on 10 different webshops/domains, with all different URL's.

When I try to implement your solution, it doesn't get through: After entering the DAX-expression, for 5 days Power BI showed the message "Complete edit / working". On the 5th day I cut it off / canceled.

Best regards,

Michael

[bcdobbs]

In the urls are the product numbers always proceeded by lid= ?

You need to extract them into their own column. My first thought would be try doing it in power query (transform data) using the column from example.