Forum Discussion
Advocate IITrouble creating lookup table with unique URLs
Hi everyone,
I am still trying to create a table with a column that only lists the unique URLs from Google Analytics data. I need it as like the heart of the data model I want to set up. I filtered out everything before September last year (lot of junk), clicked Remove Duplicates and Remove Blanks a hundred times, tried pivoting and unpivoting, column and table formulas from both the forum and documentation - nothing works. When I command Keep Duplicates in the Query Editor the table turns up empty, but in the Data view I am still counting 3563 rows and 3513 distinct values. :catsad:
It shouldn't be this difficult to keep only the unique values in a column, right?
Even though the data type is text, in theory every URL should be unique. Or maybe there are like Page IDs in Google Analytics data I don't know about?
I really hope someone, anyone, can provide me with a solution a.s.a.p., because I really need to get this done. Thanks!
5 Replies
- Greg_Deckler
Community Champion
Perhaps try some trim operations on the column before you do the remove duplicates? Any way you can post a link to the data or PBIX. I'd be interested in seeing why remove duplicates is not apparently removing all of the duplicates.
- ImkeF
Use Text.Clean and Text.Trim: Check column -> Transform -> Text Column -> Format: there you see the options, one after each other
This will get rid of blanks and unprintable character as stuff like that.
Paulien1989Lol, now I have the same number of rows but with 3514 distinct values...
- Paulien1989
Holy jokes, I did it. There was a metric in the table that, for a reason God knows, didn't correspond well with the dimensions and ended up with duplicates. Removing that metric removed all duplicates. Pfew.... Thanks guys for thinking along at least!
