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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soptim_vond
Frequent Visitor

Incremental Refresh Error: Duplicates in primary key (even after remove duplicates)

Hi folks,

 

I´m having the following problem with incremental refresh: 

 

My model includes a table in which I create a unique key by concatinating a date column with an id column (which results in multiple rows in the key column) and then adding a step "remove duplicates" which (as expected) results in a unique key column.

However, as soon as I use incremental refresh (keep last 10 days and refresh / add 1 day) I get an error message saying that there are duplicates in this column. 

Is it possible that the "remove duplicates" step is not performed on the whole data at the end (containing historical and refreshed data) but that it is actually performed once for historical data and once for new data, which are then appended resulting in remaining duplicates?

 

How would you suggest to prevent that from happening?

 

Please find below the m-code of the table in question with the relevant part highlighted in red.

 

Thanks a lot in advance,

 

Nico

 

M Code:

 

 

let

DatenAb=Text.From(Date.From(RangeStart)),
DatenBis=Text.From(Date.From(RangeEnd)),
Mandant="AbsatzPrognose",
Segment=Segmentfilter,

Quelle = Oracle.Database(Datenbank,
[Query=
"SELECT
mandant,
commodity,
vertragsnummer,
segment,
vertragsnehmer,
vertragsnehmer_knr,
anschlussnehmer,
anschlussnehmer_knr,
zp_id,
malo_id,
malo_zaehlpunktbezeichnung,
malo_marktlokation_ident,
rgzs_name,
land,
ort,
plz,
strasse,
haus_nr,
haus_nr_e,
zaehlverfahren,
ht_arbeit,
nt_arbeit,
kundenwert,
prog_param_entn_kurzfrist,
prog_param_entn_mittelfrist,
prog_param_entn_langfrist_norm,
prog_param_entn_langfrist_kalt,
prog_param_entn_langfrist_warm,
prog_param_eins_kurzfrist,
prog_param_eins_mittelfrist,
prog_param_eins_langfrist_norm,
prog_param_eins_langfrist_kalt,
prog_param_eins_langfrist_warm,
tag

FROM VIM_OWNER.V_INSIGHT_PROGNOSE_CHECK pp
WHERE pp.MANDANT = '"& Mandant &"'
AND pp.SEGMENT = '"& Segment &"'

AND pp.TAG >= to_date('"&DatenAb&"', 'dd.mm.yyyy')
AND pp.TAG < to_date('"&DatenBis&"', 'dd.mm.yyyy')
"
, HierarchicalNavigation=true]),
#"Gefilterte Zeilen" = Table.SelectRows(Quelle, each [TAG] >= RangeStart and [TAG] < RangeEnd),
#"Monatsbeginn eingefügt" = Table.AddColumn(#"Gefilterte Zeilen", "Monatsbeginn", each Date.StartOfMonth([TAG]), type datetime),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Monatsbeginn eingefügt",{{"Monatsbeginn", type date}}),
#"Zusammengeführte Spalte eingefügt" = Table.AddColumn(#"Geänderter Typ", "Key_Malo_Monat", each Text.Combine({Text.From([MALO_ID]), Text.From([Monatsbeginn], "de-DE")}, ""), type text),
#"Entfernte Duplikate" = Table.Distinct(#"Zusammengeführte Spalte eingefügt", {"Key_Malo_Monat"})
in
#"Entfernte Duplikate"

4 REPLIES 4
jeroen_guns
Frequent Visitor

Having the same issue. Error appears when there are multiple steps preformed in power query. With full load the dataset loads just fine. Another MS product that doesn't work as advertised.
Can thus only be implemented for tables with no extra power query steps. (Adding a remove duplicate(distinct) doesn't work.

Try this - Convert your power query to a Dataflow. You will notice that the range filters will automatically move to the end of the M chain, preventing you from adding steps after. 

 

Incremental refresh works as designed. If that's a good design or not is indeed debatable. 

lbendlin
Super User
Super User

Your issue is probably unrelated to incremental refresh.  Run a fixed query (with hard coded values instead of  RangeStart and RangeEnd) and you will likely see the same issue.  It can also be related to null values in your index column

Thanks for your suggestion, however that is exactly what I have done in order to rule out other causes. Refreshing the data for the same timespan without incremental refresh is completed successfully. Any other ideas?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors