Hello everyone,
we've some issues regarding our M Script with the MySQL Connector.
I always thought that Power BI executes M Script internally. So it first pulls the data of the data source (MySQL DB in our case) and then does the transformation. In our case we had a concatination of two columns and a sort to make the Power BI File a little smaler, because there are a lot of same long texts in one column. This makes the following steps:
1. Pull Data
2. Navigation
3. Remove some Columns
4. Concatination
5. Sort
The daily schedule today failed because the MySQL temp Folder was full, so I've looked into the error log of the Database.
I noticed that the query in the error is not like Select * from xxxx but like
Select
'_'.a,
'_'.b,
'_'.c
FROM
(
Select
'_'.a,
'_'.b,
Concat('_'.a,'_'.b ) as c
From XXX
) Order by '_'.a
Which is definitely not what I've expected. I know that it usually makes sence to let the Database do the sorting, but in our case this special Database is not designed to perform this kind of workload.
Is there any way to force Power BI to do the sorting in Power BI Memory and not by the Database? Is there a fixed number of steps which are converted to SQL? Because we have a transformation with more steps (and also more rows) where the last step is also a sort, which didn't fail.
Best regards
Chris
