cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
renanc Frequent Visitor
Frequent Visitor

(Error to convert nvarchar to Datetime) Conversão de dados tipo nvarchar para datetime: PowerQuery M

Quando a power query tenta integrar os dados através da fonte/source SQL aparece um erro de conversão de data por parte do power bi.
É importante observar que na query do SQL não está dando problemas, eu verifiquei.
Provavelmente é quando o power bi tenta converter os dados nvarchar para data porque provavelmente a data está em formato de texto para o PBI.

 

errorM.jpgErro de conversãosource.PNGExemplo de estratificação da table direto na fonte do SQL


Query (atual) pelo M: 

 

 

Fonte = Sql.Database("serverx", "xx"),
dbo_vw_PS = Fonte{[Schema="dbo",Item="dbo_vw_PS"]}[Data],
#"Linhas Filtradas" = Table.SelectRows(ddbo_vw_PS, each [DataMovimentacao] > #datetime(2019, 6, 1, 0, 0, 0)),

 


Spoiler
Também vou acrescentar alguns passos que já foram feitos na tentativa de me ajudar em outro forum, MAS não deram certo:

 

1)

 

 

let
Fonte = Sql.Database("serverx", "xx"),
dbo_vw_PS = Fonte{[Schema="dbo",Item="dbo_vw_PS"]}[Data],
CastToDate =
Table.TransformColumnTypes(
dbo_vw_PS,
{{"DataMovimentacao", type date}}),
#"Linhas Filtradas" =
Table.SelectRows(
CastToDate,
each [DataMovimentacao] > #datetime(2019, 6, 1, 0, 0, 0)),

 


e segunda tentativa:
2)

 

let
Fonte = Sql.Database("serverx", "xx"),
dbo_vw_PS = Fonte{[Schema="dbo",Item="dbo_vw_PS"]}[Data],
CastToDate =
Table.TransformColumns( // Note we're using Table.TransformColumns instead of Table.TransformColumnTypes
dbo_vw_PS,
{{"DataMovimentacao", each DateTime.FromText(_, "pt-BR"), DateTime.Type}}),
#"Linhas Filtradas" =
Table.SelectRows(
CastToDate,
each [DataMovimentacao] > #datetime(2019, 6, 1, 0, 0, 0)),

 


 Obs: esses passos acima deram problema no ciclo através da linguagem M.


Obrigado!

 

 

3 REPLIES 3
renanc Frequent Visitor
Frequent Visitor

Re: (Error to convert nvarchar to Datetime) Conversão de dados tipo nvarchar para datetime: PowerQue

ADDING ENGLISH TEXT:

When the power query tries to integrate data through the source SQL, a power bi date conversion error appears. It is important to note that in the SQL query is not giving problems, I checked. This is probably when power bi tries to convert nvarchar data to date because the date is probably in PBI text format.

Community Support Team
Community Support Team

Re: (Error to convert nvarchar to Datetime) Conversão de dados tipo nvarchar para datetime: PowerQue

Hi @renanc ,

If I understand your scenario correctly that when you get the data from SQL Server Database, the date column has error ?

Normally, if the date column in SQL Server is date type, it still will be the date type when loading in power bi.

For your scenario, since the date column is identified as text type when loading in Power query, I think you could use FARMAT () function to convert the text to date.

Please refer to this thread.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
renanc Frequent Visitor
Frequent Visitor

Re: (Error to convert nvarchar to Datetime) Conversão de dados tipo nvarchar para datetime: PowerQue

Thank you @v-piga-msft for your reply.

 

I have posted with english body here:

https://community.powerbi.com/t5/Power-Query/Error-to-convert-nvarchar-to-Datetime/m-p/792069#M26551

 

But no, this thread wont solve my issue, because it is a power query problem. 

 

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors