Create a date column based on two numeric fields in DirectQuery mode.

[telesforo1969]

I have a table connected via DirectQuery to Power BI, and I have a numeric field (periodo) and a numeric field (Anio). With these values, I need to create a date field for data analysis. I am attaching an image of the concatenation I am doing, but it indicates that I cannot convert them unless I import the table. Is there any other way to create the date field?te the date field?

[KarinSzilagyi · Accepted solution]

Ah, sorry - I missed that you're using DirectQuery mode. 
In that case you could try to add the column via SQL through the advanced connection settings instead:

Then remove the Navigation-step below your Source-Step.

[SwarnaTeja]

HI @telesforo1969 ,

Yes Direct Query Mode doesn't support data type conversions.

Can you try doing this conversion in the modeling tab of the report after loading the data and not in the Query Editor.

Hope that helps!

[KarinSzilagyi]

Hi @telesforo1969, Try this: 

each Date.FromText("01/"&Text.From([Periodo]) &"/"& Text.From([Anio])))

You are trying to build a string from numeric values and need to additionally transform it into a valid date. Since you're using numeric columns for Periodo and Anio you can't just string them together like text.

[KarinSzilagyi · Accepted solution]

Ah, sorry - I missed that you're using DirectQuery mode. 
In that case you could try to add the column via SQL through the advanced connection settings instead:

Then remove the Navigation-step below your Source-Step.

[telesforo1969]

I get the following error. I've checked my table name thinking that could be the error, but it's not.

[KarinSzilagyi · Accepted solution]

Hi @telesforo1969, according to the error-message the table doesn't exist in the database with that name. Are you sure that your is in the dbo-Schema? 

You can confirm which schema your table belongs to if you connect normally via the SQl-connector and check the code in the "Navigation"-step (or by checking it directly on the SQL Server if you can access it)

[telesforo1969]

Thank you very much, with your support I have converted two numeric fields to date in

DirectQuery Mode