Table simple pivot results in: Unknown column '_.t0_0' in 'order clause'

[greyniall]

Hi all,

I'm having an issues during a table pivot which has me stumped. 

The table I'm using is a merged table retrieved from a MySQL source. The table looks like this:

If apply the pivot on the column "field" using the values "value", I get the following error:

Amoung others, I have tried: checking for spaces (there are none in the column field), refreshing all, restart PowerBI, reboot, deleting credentials, setting utf8 text encoding before retrieving the table. I understand it is a SQL error but I can't figure out why this error is still popping up way after the sql query itself has been run. I also tried creating a blank table with this data in it and that works fine so it must have something to do with the fact that sql is the source. 

Any ideas here? 

[greyniall]

Hi @jbwtp and @Anonymous ,

Thanks for your suggestions! I tried them both and there was no difference. I had already tried clearing all credentials (both in the file as well as global). I tried again and no luck. Applying: "Remove Other Columns" before the pivot also resulted in the same error. 

Any additional ideas? 

Cheers

[jbwtp · Accepted solution]

Hi @greyniall,

can you see anything in the Native Query:

My next step would be to remove all other columns rather than field and value and try pivoting.

If this is failed, I would take top 3 rows and try again to see if this works (i.e. limit it to somethign that you can see on screen entirely).  

If this fails, I would try Table.Buffer the 2 column and 3 row table (which would in theory force evaluation of all cells) and then pivot to see if this helps.

Cheers,

John

[greyniall]

@jbwtp wins the price. Thanks you John!

I did not know of the buffer feature and it makes sense and is exactly what I was looking for. Since the table is small it's fine to buffer it at this point and it worked! See result below. 

Still curious why this happens though. If anyone has any additoinal ideas, I'm all ears. 

Removing a line one by one (your first suggestion) resulted in a sucessful pivot each time. Which is strange becuase it therefore doesn't seem to be a single value causing the error. I also need all these columns so though it's interesting to test, it didn't helpt me get to the end. 

[Anonymous]

Hi @greyniall ,

// I have tried: checking for spaces (there are none in the column field), refreshing all, restart PowerBI, reboot, deleting credentials, setting utf8 text encoding before retrieving the table.

Have you ever tried deleting the local and global permission of this data source? Clear both them and then re-enter the credentials when refresh.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[jbwtp]

Hi @greyniall,

Could you please try "Remove Other Columns" before pivoting? I think that MySQL is trying to push a service column via SELECT * FROM and PBI can't process it. This should convert the statement to something like SELECT doctype, field, value, source FROM, which would explicitely tell PBI to ignore hidden columns.

Cheers,

John