Forum Discussion
Power Query has fewer rows than power bi datamodel? #postgreSQL
- 2 years ago
Hello again,
I stripped my problem down to the (I think) smallest piece of data an M-code. I attach all necessary files in a ZIP-file. It woulld be so nice if someone with a running postgreSQL instance can reproduce my problem - and if you can: someone can tell me where to send a bug report to microsoft?
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
PS: I was not able to insert attachments to this post - I always get exceptions like "txt is not supported, pbix is not supported,...". So here is a dropbox link:
Sorry, but I have to come back with my problem:
Especially when it comes to duplicates (due to a buggy data connector...?) the solution cannot be to just use "Remove Duplicates". Because that means, that each and every row is loaded to memory - especially (!) when it comes to big datasets this cannot be the solution ?!
Here is my M-Code:
Query no1 (base query - not loaded):
############################
let
Quelle = PostgreSQL.Database("localhost", "postgres", [CreateNavigationProperties=false]),
public_costitems = Quelle{[Schema="public",Item="costitems"]}[Data]
in
public_costitems
Query no 2 (loaded)
########
let
Quelle = _costitems,
#"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"id", "kind"}),
#"Sortierte Zeilen" = Table.Sort(#"Andere entfernte Spalten",{{"id", Order.Ascending}})
in
#"Sortierte Zeilen"
In Power BI I filter for an element with id=1495: the row is doubled. For no reason!
I have a guess - not really an explanation, but kind of a reason:
I logged all SQL-statements. I saw to sql-statements (pls ignore the quotation marks - I copied the statements out of the log files):
SELECT
""$Ordered"".""id"",
""$Ordered"".""itemnumber"",
""$Ordered"".""overwrittenvalue"",
[...]
from ""public"".""costitems"" ""$Ordered""
order by ""$Ordered"".""id""
limit 4096"
And a second one which is nearly the same - onyl the limit/offset changed:
select
[...]
from ""public"".""costitems"" ""$Ordered""
limit 9223372036854775807 offset 4096"
==>Why does it break the fold? Nothing special in the the query?
I looked at the datatypes of the columns of the tablle and I found, that there is a special datatype used for the column [kind] which is an "enumeration" in postgres. When I changed the selected columns and load e.g. the [orderid] instead of the column [kind], the rows and not doubled anymore. I can take an arbitrary set of columns of this table - as long as I do not use the field [kind] the query is folded and everything works fine. Only if I take the field [kind] in the selection, some rows are doubled.
But: I need this column. And it seems like a bug in the data connector. Anyone who can help me or give me a hint, where I can post the problem, if this forum is the wrong one?
Thanks
Holger
Hello again,
I stripped my problem down to the (I think) smallest piece of data an M-code. I attach all necessary files in a ZIP-file. It woulld be so nice if someone with a running postgreSQL instance can reproduce my problem - and if you can: someone can tell me where to send a bug report to microsoft?
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
PS: I was not able to insert attachments to this post - I always get exceptions like "txt is not supported, pbix is not supported,...". So here is a dropbox link:
- ppvinsights2 years ago
Helper III
Made a new topic with the problem