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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 194 members 2,308 guests
Please welcome our newest community members: