cancel
Showing results for 
Search instead for 
Did you mean: 
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"

2 REPLIES 2
lbendlin
Super User III
Super User III

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors