Forum Discussion

Elsie14's avatar
Elsie14
Icon for Advocate I rankAdvocate I
9 years ago

Date settings in Power BI

I have a problem with the date settings in Power BI. Currently I have a dataset which have two colums with the data type "Date", namely LastLogonDate and CreationDate.   In my report I have filtere...
Need Help
Show and Tell
Tips and Tricks
Elsie14's avatar
Elsie14
Icon for Advocate I rankAdvocate I
9 years ago

I'm sorry, my last line of the editor was indeed:

 

#"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns2", {{"LastLogonDay", type date}}, "en-US")

 

Unfortunately, also without results. Still reads it as 1st of November 2017:(

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

It's hard to tell, but I found a possible clue:  would it be possible that only the DD/YYYY part of the date is converted to date, e.g. after data transformation, especially splitting?

 

I ran the following code, which returns November 1, 2017 for "11/2017" (and errors for "13/1/2017"):

 

let
    Source = {"1/13/2017", "13/1/2017", "11/2017", "1/11/2017"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}, "en-US"),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type with Locale", "Month Name", each Date.MonthName([Column1]), type text)
in
    #"Inserted Month Name"