Forum Discussion

dienursen's avatar
dienursen
New Member
2 years ago
Solved

How to Remove Duplicates from PowerBI SQL Direct Query Table

Hello All,

 

I am creating a dashboard using an SQL Database, so there is no Power Query where I can work on an data query.

In the dataset I have several alphanumeric IDs with several versions of them. But I need only the last versions of all these IDs. So I need to remove the duplicates of these IDs with the consideration that I need only the last versions.

 

For example, the alphanumeric ID is ABC2010001111, this one has 4 versions, 00, 01, 02, 03.

I only need the latest and most updated version to have a reliable dashboard. So  ABC2010001111/03 is the one I keep. all others must be removed.

Another might have 20 versions, or 2 versions. So there is no point in saying "use always thr 4th version."

 

Any idea how this might work?

 

Thank you in advance,

 

Best regards,

Nursen

Need Help
Tips and Tricks
  • johnbasha33's avatar
    johnbasha33
    2 years ago

    dienursen 
    Hi, since you are using sql source as direct query, 1st approach is to get the solution form sql query it self.
    WITH LatestVersions AS (
    SELECT
    ID,
    Version,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Version DESC) AS RowNum
    FROM
    YourTable
    )
    SELECT
    ID,
    Version
    FROM
    LatestVersions
    WHERE
    RowNum = 1;

    or 2nd approach is to use DAX

    Create a Calculated Table:
    LatestVersions =
    SELECTCOLUMNS(
    SUMMARIZE(
    'YourTable',
    'YourTable'[ID],
    "LatestVersion", MAX('YourTable'[Version])
    ),
    "ID", [ID],
    "Version", [LatestVersion]
    )
    and then calculated column




    LatestVersion =
    CALCULATE(
    MAX('YourTable'[Version]),
    ALLEXCEPT('YourTable', 'YourTable'[ID])
    )

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

2 Replies