Column Type Mismatch Between Desktop and Query Editor

[adammatern]

I used the power query editor to change a list of dates from the auto generated text column type to the corrected short date format, but for some reason it does not update on the main Power Bi Desktop page. (I have clicekd the close and apply button once I'm done in the power query editor)

I have done this same process for other columns and never had an issue until now.

[v-alq-msft]

Hi, @adammatern 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may use Date.FromText to convert the data type from text to date and give the column a 'Date' data type. You can add a new step as below.

Table.TransformColumns(#"Changed Type",{"Date",each Date.FromText(_),type date})

Result:

Best Regards

Allan

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

[adammatern]

The formula you sent gives me an error (and I did adapt it for my column names when I tried it), but the data is already listed as a date format on the editor. The main screen where I am building the dashbaord still will not show the "response delivered" data as a date though even though I've done this for 2 of the other columns here and they worked perfect like normal.

Any help is greatly appreciated! 

[HotChilli · Accepted solution]

Do you have a date table in the model (and is there a relationship to 'Response Delivered')?

[adammatern]

Yes this fixed the issue, thank you so much @HotChilli !!

[adammatern]

I think that's what's causing the issue! This is part of two appended tables and Bi chose the "response delivered" column as the key

[pranit828]

HI @adammatern 

Change the dataype on the PBI desktop instead of power query editor.

[adammatern]

Unfortunatly the date was already selected for the data type/format, but it still does not show as a date with the hierarchy on the main page

[mahoneypat]

The query editor just changes the data type.  After data load, in Data View, click on the column and the Format tab to change it to your desired format.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat