Trouble creating lookup table with unique URLs

[Paulien1989]

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.  

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!

[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.

[Paulien1989]

Lol, now I have the same number of rows but with 3514 distinct values...

[ImkeF]

Hm - so that only eliminated only 1 uncatched dup.

Somewhere I've read that the counts shown for the tables are not reliable.

Check the following: Duplicate your query and try to connect both (same) queries on 1:1. If it works: Pls make bug-report here: https://community.powerbi.com/t5/Issues/idb-p/Issues - Thx

[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!

[Greg_Deckler]

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.