Column calculated from function stored in MySQL

[p_stathopoulos]

I need to calculate/create a new column in a table.

Each row (will be) is the result from a function (currently stored in MySQL Database - the same i am connected to get the table)

Table SALES

Product	Date	Sales
p1	1/1/2000	100
p1	1/4/2000	210
p2	1/4/2000	200

The new column let's name it COST and is calculated as: function (product, date) and return the cost (is a FIFO calculation)

How can i import/replicate on PowerBI ?

Product	Date	Sales	Cost
p1	1/1/2000	100	80
p1	1/4/2000	210	170
p2	1/4/2000	200	160

I tried to load the function, (ok so far)

Invoked and added to the QUERIES (so it show up to New column as function)

The preview looks fine, but when i hit close & apply to create the actual column the i get an error: 

[DataSource.Error] MySQL: Fatal error encountered during data read

The table is over 210.000 row, maybe is this the problem ?

[mwegener]

Hi @p_stathopoulos 

could you post some screenshots from what you have done at Power Query?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[p_stathopoulos]

Ok,

The steps i took:

I load the function from Source / Recent source (i have some other tables loaded before)

I also test with parameters if is working right

I add a new column on the table i want

 Everythinglook good so far.

Then i hit save & apply  and return to Power Bi Desktop and it start to load / calculate

After the end (i think) give me the error:

[mwegener · Accepted solution]

Possibly the error is generated by a row that is not included in the power query preview (1.000 rows).

Maybe you can check this, with a limited set of rows.

[p_stathopoulos]

Is there a way to check it or a iferror function to avoid it?

As i see after "Close & Apply" it goes up to 100.000 + row and after that it gives me the error (i can't see in what line the error occured) 

[p_stathopoulos]

I remove 44.000 rows and now there is no error.

Is there a way to trace it (so i can find the real cause) other than remove 10-20-30-40.000 row each time and wait for the calculations to end (and repeat to narow it down to a single row), to find in what row is the error ?

[p_stathopoulos]

My problem is solved, but i din't find the cause. Maybe i try to delete fewer rows until i narrow it down to a couple of rows

[mwegener]

Hi @p_stathopoulos ,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.