How to Remove Duplicates from PowerBI SQL Direct Query Table

[dienursen]

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

[johnbasha33 · Accepted solution]

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

[amitchandak]

@dienursen , You can not do this in Power Query in case of direct query mode, you need have DAX measure to display

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...