DirectQuery Formatting problem

[rwcampbell]

Hi,

data coming in from datasource as dd-mm-yyyy and when i change on the power query or anywhere it wont accept it.

on PQ it shows as it cannot add it due to being a direct query table.

any suggestions welcome to change it from this style into a more date related format.

thanks.

[PowerBigginer]

@rwcampbell In Transformdata duplicate the date column and try to change datatype to duplicated column and use that column I guess it will work.
If it helps, please mark it as the Accept it as solution.
For more real-time scenarios, visit https://powertipstricks.blogspot.com/.
Thanks & Regards.

[rwcampbell]

Tried your duplicate and change and got this error.

[v-yilong-msft]

Hi, @rwcampbell

Based on the information you provided, I imported the data with dates through Direct Query in SQL Server.

Disappointingly trying to make changes to date types in Power Query in Direct Query mode doesn't seem to be very feasible at the moment, you can refer to this documentation：Direct Query limitations.

If you want to change the Format, you can do so on Visualizations.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rwcampbell]

Hi @v-yilong-msft,

our data is linked to DQ as dd-mm-yyyy which is causing the headaches - when i try to modify it on visualisations i get the error i shown in previous replies.

due to the nature of the work - i cannot provide data or a pbix as it is part of a full dataset, just looking advice or leads to try and overcome this issue.

thanks.

[v-yilong-msft]

Hi, @rwcampbell 

Based on the information you've now provided, the modifications I've made to the date type on Visualizations based on my existing data hold up.

I think the reason you are unsuccessful is because the dataset is in the form of text when imported in Direct Query mode, so if you want to convert under the date type it will report an error, but if you want to change it in Power Query you can't do that either, so I would suggest that you can try it by selecting the import mode in the first place.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PijushRoy]

Hi @rwcampbell 

What is the data type you want to update?
After loading the data in Power BI Desktop, you can change the Datatype

If your requirement is solved, please make sure to MARK AS SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.

Thanks
Pijush
www.MyAccountingTricks.com 
https://www.youtube.com/MyAccountingTricks

[rwcampbell]

hi,

it is text format and when i do that to date i get this error:

any other way results in me moving it from Direct Query to Import which isnt good as this table needs to be updating every 30 minutes as it links dates for another table.