Hi forum,
I already posted this topic in another thread - but I think it is better to create a new one with the problem stripped down to the main problem, with (hopefully) can be replicted by someone in this forum:
I have a problem with the postgresql connector loading data from a table. I load the data in a base-query which is referenced by another query. And magically the rows are duplicated if loaded to power bi (is not visible in power query) - and I cannot find the problem or a solution. A transformation step "delete duplicates" is not a solution (before doing that I use a native sql query). My initial problem was way more complicated having a couple of joins - I was not able to figure out why I have duplications. Now I stripped down the problem to an easy transformation.
You can find my example files in my dropbox:
https://www.dropbox.com/scl/fi/klcq6rusf83pgu7wj6ku6/postgreSQL_RowDuplication.zip?rlkey=imu2dp8qxlc...
The file contains the following files:
1) createDatatype: This creates an enumeration datatype. I need to select this column to reporoduce the error.
2) the ddl of the table with the data
3) the insert statements (about 23.000 rows with just 3 Columns)
and for sure: the power bi file. You just have to change the database server and the database.
I found these obervations:
- If there is no index on the "id" column, the duplication does not happen
- The transformation "select columns" is necessary. Otherwise no duplication happens
- you need to select the enumeration column. Otherwise no duplication shappens.
- the sort is necessary. Otherwise no dulication happens
I think the reason is hidden behind
- query folding
- paging with using the index
- the enumeration
Thanks everybody for your support!
Holger
