Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
dienursen
New Member

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

1 ACCEPTED SOLUTION
johnbasha33
Super User
Super User

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

View solution in original post

2 REPLIES 2
johnbasha33
Super User
Super User

@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
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.