Remove duplicates record with direct query

hankl33
Posts: 2
Joined: Mon Oct 26, 2020 10:57 pm

Remove duplicates record with direct query

Postby hankl33 » Mon Oct 26, 2020 11:36 pm

Hi All,

I have source data from a relational DB and try to form a data set for my report, but I'm stuck on tables with duplicate records which I don't want to load them in before modeling the data, so I'm thinking to remove them in transformation stage (Power Query to me).

I know this is easy by removing duplicates with index column or sorting with Table.Buffer, however my users want to view the report in real time which means direct query is the only options.

Sorry I'm new to Power BI and would glad to know if anyone could share any best practice on removing duplicates, if this should be done in transformation? Or Modelling? Or even before get data with Customized SQL statement?

Please if anyone could help me out by giving me some advice?

At last, below is sample data I have and what it should look like eventually.

Image

And the error message that ask me to change from Direct Query to Import Mode

Image

My M-Query
let
Source = MY_SOURCE,
#"Removed Other Columns" = Table.SelectColumns(Source,{MY_COLUMNS}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Removed Other Columns",{{"CREATED_DATE", Order.Descending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"REFERENCE_NO"})
in
#"Removed Duplicates"

MattAllington
Posts: 1144
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Remove duplicates record with direct query

Postby MattAllington » Tue Oct 27, 2020 6:34 am

There are current 2 options (as I guess you know). Load and transform the data, or use the data as it exists in direct query mode. The way to solve it is have someone write a view in the database that has what you need.

Microsoft has announced composite models that will allow a combination of import and direct query. When that is released, you should be able to do it in Power Query
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

hankl33
Posts: 2
Joined: Mon Oct 26, 2020 10:57 pm

Re: Remove duplicates record with direct query

Postby hankl33 » Tue Oct 27, 2020 12:24 pm

Thanks Matt, I guess I will go ahead load and transform it.

Also thanks for sharing composite models.

MattAllington
Posts: 1144
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Remove duplicates record with direct query

Postby MattAllington » Tue Oct 27, 2020 6:35 pm

I just happened to watch this today. It talks about the new features coming soon. https://youtu.be/Vzr9a5JjwHw
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests

cron