CLR type

[Elage37]

Hello I have a problem with the display of time type data. This is an OLE DB connection to an HFSQL database (windev). The error message I get when I try to view the data on power query is " 'We do not support CLR type 'Microsoft.Mashup.Engine1.Runtime.ValueException'".' ".. I'm stuck because all my analysis is going to be about the in and out times of the database
If anyone has faced this before please help me

Bonjour j'ai un problème avec l'affichage des données de type heure. Il s'agit d'une connexion OLE DB vers une base de données HFSQL ( windev). Le message d'erreur que j'ai quand j'essaye de voir les données sur power query est " 'Nous ne prenons pas en charge le type CLR « Microsoft.Mashup.Engine1.Runtime.ValueException ».' ".. Je suis bloqué parce que tout mon analyse va porter sur les heures d'arrivée et de descente de la base de données
Si quelqu'un a déjà été confronté à cela help me please

[lbendlin]

Instead of consuming the table can you run a SQL query that casts the values in a more agreeable format?

[Elage37]

Hi,
in power query?? how to do?

[lbendlin]

Show your current code (sanitized)

[Elage37]

let
Source = OleDb.DataSource("provider=PCSoft.HFSQL;initial catalog=IBP_DATA;data source=192.168.50.22"),
IBP_DATA_Database = Source{[Name="IBP_DATA",Kind="Database"]}[Data],
POINTAGE_Table = IBP_DATA_Database{[Name="POINTAGE",Kind="Table"]}[Data]
in
POINTAGE_Table

[lbendlin]

In the connection settings you can already specify a custom query.

[Elage37]

in this case i want to use the table "pointage " already existing. 

What custom query can i use for changing perhaps for casting values?

[lbendlin]

That depends on the SQL flavor used by your data source.

[Elage37]

What do you need right now for helping? 
what do i do for giving you the SQL flavor?
i have this in this window. What do we write on the custom query?

[lbendlin]

I don't know how PCSoft.HFSQL works.  Something like

SELECT CAST([HEUREARRIVEE] as text) from POINTAGE

[Elage37]

Bonjour,
toujours dans la méme optique . Ayant un fichier .sql , comment faire pour l'intégrer dans power query. Le but c'est de voir s'il va pouvoir enlever la "complexité" des colonnes de la base initiale en des colonnes de type heure simple et exploitable dans power query

[lbendlin · Accepted solution]

Most connectors for SQL style databases allow you to specify a "native query" when you create the connection.

For SQL Server the field is "SQL Statement".

Alternatively you can implement your SQL logic in a view at the data source, and then in Power BI you can consume the view as if it were a table.

[Elage37]

thank you for the beginning of track. I will test that and continue to look the solution. I will come back to you if this did not ^pass

[alexTaran]

Hi,Elage,

have you solve this issue? Have the same trouble unfortunatelly

[Elage37]

nop unfortunatly. i was forced to export in excel for using the colums in the table 

[alexTaran]

You know, my story has ended succesfully

https://community.fabric.microsoft.com/t5/Developer/Postgre-quot-We-do-not-support-CLR-type-Microsof...

[Elage37]

Thx you i will try that and i will let you know! thx