I have now limited the error down a bit.
The case sensitivity should not be the issue, since the resulting duplicates are recognized as duplicates by the function EXACT which is case-sensitive
Here is what happens:
I start with 75 unique lines, the csv file that is to be added has 48 lines (all unique)
1.) When I run the query once all 48 lines are imported. So I end up with 123 lines - which is correct.
2.) If I run the query again (csv file is not changed), 19 lines are importet again - on top of the existing - -resulting in 142 lines.
These 19 lines are now duplicate, which should not happen. **
3.) If I run the query again, the result stays at 142 lines!
This makes me think, that in the query that collects my exisiting data from the table (the 142 in above mentioned step 3) the duplicates are recognized but the 19 coming from the csv file are not recognized as being already there. This would explain why the 19 are added twice but then not more often if the query is run again.
** I had a look at those duplicates:
Visually the look identical.
If I concatenate all columns of two duplicate entries the result
is NOT recognized as being identical by conditional formatting (highlight double entries)
IS POSITIVLY recognize as being identical by the function EXACT
All in all it looks to me as if some (invisble) cell value is only set when the query writes the data back in the table. This way we could have different values at the point when Table.Distinct is executed and then have identical values in the final table, which are also recognized as identical when the query runs once more.
Here are the two queries:
let
Quelle = Excel.CurrentWorkbook(){[Name="_Table"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Hersteller", Int64.Type}, {"HerstBez", type text}, {"Bestellreferenz", type text}, {"Bestelldatum", type date}, {"AuftragsArt", type text}, {"AuftragsArtTxt", type text}, {"Auftrag", Int64.Type}, {"AG_ID", Int64.Type}, {"AG_Titel", type text}, {"AG_Name1", type text}, {"AG_Name2", type text}, {"AG_Strasse", type text}, {"AG_PLZ", Int64.Type}, {"AG_Ort", type text}, {"AG_Land", type text}, {"WE_ID", Int64.Type}, {"WE_Titel", type text}, {"WE_Name1", type text}, {"WE_Name2", type text}, {"WE_Strasse", type text}, {"WE_PLZ", Int64.Type}, {"WE_Ort", type text}, {"WE_Land", type text}, {"Lieferung", Int64.Type}, {"Lieferdatum", type date}, {"LieferPos", Int64.Type}, {"Material", type text}, {"PZN", type text}, {"HerstellerArtNummer", type text}, {"Material Bezeichnung", type text}, {"Charge", type text}, {"Verfallsdatum", type date}, {"Liefermenge", Int64.Type}, {"Einheit", type text}, {"Kategorie", type text}, {"FakturaArt", type text}, {"FakturaArtTxt", type text}, {"FaktPosTyp", type text}, {"FaktPosTypTxt", type text}, {"Faktura", Int64.Type}, {"FakturaDatum", type date}, {"FakturaPos", Int64.Type}, {"FakturaMenge", Int64.Type}, {"Nettopreis", type number}, {"Nettowert", type number}, {"Währung", type text}, {"USt_Satz", Int64.Type}, {"Zahlungsbed. Code", type text}, {"Zahlungsbedingung", type text}, {"RE_ID", Int64.Type}, {"RE_Titel", type text}, {"RE_Name1", type text}, {"RE_Name2", type text}, {"RE_Strasse", type text}, {"RE_PLZ", Int64.Type}, {"RE_Ort", type text}, {"RE_Land", type text}}),
#"Angefügte Abfrage" = Table.Combine({#"Geänderter Typ", #"2_Faktura_Daten_Detail_CSV"}),
#"remove duplicates" = Table.Distinct(#"Angefügte Abfrage")
in
#"remove duplicates"
let
Quelle = Csv.Document(File.Contents("X:\...\2_Faktura_Daten_Detail.csv"),[Delimiter=" ", Columns=62, Encoding=1200, QuoteStyle=QuoteStyle.None]),
#"Höher gestufte Header" = Table.PromoteHeaders(Quelle),
#"Entfernte Spalten" = Table.RemoveColumns(#"Höher gestufte Header",{"ZF1", "ZF2", "ZF3", "ZF4", ""}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Entfernte Spalten",{{"Hersteller", Int64.Type}, {"HerstBez", type text}, {"Bestellreferenz", type text}, {"Bestelldatum", type date}, {"AuftragsArt", type text}, {"AuftragsArtTxt", type text}, {"Auftrag", Int64.Type}, {"AG_ID", Int64.Type}, {"AG_Titel", type text}, {"AG_Name1", type text}, {"AG_Name2", type text}, {"AG_Strasse", type text}, {"AG_PLZ", Int64.Type}, {"AG_Ort", type text}, {"AG_Land", type text}, {"WE_ID", Int64.Type}, {"WE_Titel", type text}, {"WE_Name1", type text}, {"WE_Name2", type text}, {"WE_Strasse", type text}, {"WE_PLZ", Int64.Type}, {"WE_Ort", type text}, {"WE_Land", type text}, {"Lieferung", Int64.Type}, {"Lieferdatum", type date}, {"LieferPos", Int64.Type}, {"Material", type text}, {"PZN", type text}, {"HerstellerArtNummer", type text}, {"Material Bezeichnung", type text}, {"Charge", type text}, {"Verfallsdatum", type date}, {"Liefermenge", Int64.Type}, {"Einheit", type text}, {"Kategorie", type text}, {"FakturaArt", type text}, {"FakturaArtTxt", type text}, {"FaktPosTyp", type text}, {"FaktPosTypTxt", type text}, {"Faktura", Int64.Type}, {"FakturaDatum", type date}, {"FakturaPos", Int64.Type}, {"FakturaMenge", Int64.Type}, {"Nettopreis", type number}, {"Nettowert", type number}, {"Währung", type text}, {"USt_Satz", Int64.Type}, {"Zahlungsbed. Code", type text}, {"Zahlungsbedingung", type text}, {"RE_ID", Int64.Type}, {"RE_Titel", type text}, {"RE_Name1", type text}, {"RE_Name2", type text}, {"RE_Strasse", type text}, {"RE_PLZ", Int64.Type}, {"RE_Ort", type text}, {"RE_Land", type text}})
in
#"Geänderter Typ"