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
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

What data type is your input: text or date alteady?

 

Did you add "en-US" with every data type change to date?

 

Can you share some relevant parts of your code?

 

Are you refering to DAX or to Power Query?

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

What data type is your input: text or date alteady?

 

The original input was of type text. I have converted it to type date.

 

 

Did you add "en-US" with every data type change to date?

 

What do you mean with this? I can't give this parameter when I change the type in the editor.

 

Can you share some relevant parts of your code?

 

This is the code where I changed the type of these columns (from text->date/time and from date/time to date):

 

    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"LastLogonDate - Copy", type datetime}, {"CreationDate - Copy", type datetime}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"CreationDate - Copy", type date}, {"LastLogonDate - Copy", type date}}),

 

Are you refering to DAX or to Power Query?

 

Power Query

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

    Well, you can supply the culture code with every type change, but you must know where to find it.

     

     

    Or you can adjust the generated code by supplying ", <locale>"  just before the last ")":

     

        #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"LastLogonDate - Copy", type datetime}, {"CreationDate - Copy", type datetime}}, "en-US"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"CreationDate - Copy", type date}, {"LastLogonDate - Copy", type date}}, "en-US"),
    • 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"