Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
4 months ago
Solved

Date.FromText

Hi, 

 the Date.FromText function,   does it takea 'Culture'  so i know there is plenty of documenttion saying yes; so even though 

= let d = "03/Apr/2020" 
in Date.FromText( d, [Format = "dd/MMM/yyyy", Culture = "en-GB" ] )


i have yet to find a sitution whrere the culture has any effect ( or impact as everyone seems to say now) , 
also if you leave out the Format and just use culture the ineli sense pop up says Culture not supported , so does 
anyone have a definitive answer  ? 

Need Help
Show and Tell
  • Odet_Maimoni's avatar
    Odet_Maimoni
    4 months ago

    You’re not wrong - the behavior here is confusing.

     

    Date.FromText does support culture, but not in the way you’re trying to use it.

     

    A few clarifications:

     

    - The Culture parameter is mainly used when parsing ambiguous text (e.g. 01/02/2020)

    - It doesn’t reliably work when combined with a Format record like in your example

    - In some cases, IntelliSense even shows it as unsupported, which adds to the confusion

     

    In practice:

     

    - If you already specify a Format, Power Query relies on that and culture has little to no effect

    - Culture is more relevant when you don’t provide a format and Power Query needs to interpret the text

     

    So what you’re seeing is expected - culture won’t have much (or any) impact in your current example.

     

    Odet Maimoni  

    BI Engineer | Microsoft MVP  

    I share practical Power BI tips on YouTube

3 Replies

  • Odet_Maimoni's avatar
    Odet_Maimoni
    Advocate I
    4 months ago

    You’re not wrong - the behavior here is confusing.

     

    Date.FromText does support culture, but not in the way you’re trying to use it.

     

    A few clarifications:

     

    - The Culture parameter is mainly used when parsing ambiguous text (e.g. 01/02/2020)

    - It doesn’t reliably work when combined with a Format record like in your example

    - In some cases, IntelliSense even shows it as unsupported, which adds to the confusion

     

    In practice:

     

    - If you already specify a Format, Power Query relies on that and culture has little to no effect

    - Culture is more relevant when you don’t provide a format and Power Query needs to interpret the text

     

    So what you’re seeing is expected - culture won’t have much (or any) impact in your current example.

     

    Odet Maimoni  

    BI Engineer | Microsoft MVP  

    I share practical Power BI tips on YouTube

    • Dicken's avatar
      Dicken
      Post Prodigy
      4 months ago

      thanks, starting to make some progress,  so if i have 

      = let adate = "03/02/2020" in adate

      so this give ambiguous text date ;   so i a can use ;

      = let adate = "03/02/2020" in 
    Date.FromText( adate)

or specify culture; 
 let adate = "03/02/2020" in 
    Date.FromText( adate, "en-GB") 

Now as my  windows settign are win eng uk, the default is dd / MM  / yy , 
so when is use 
= let adate = "03/02/2020" in 
 Date.FromText( adate , "en-US")  the date now =   MM / dd / yy

      and now i can do what i was oriinally trying to do,  which is take a uk format as text , 
      have as proper date but in US format;   

      = #table ( type table [ adate = date] , 
{{  let adate = "03/02/2020" in 
 Date.FromText( adate , "en-US") }} )

      and yep it works when add dayys is used. 

      Richard. 

      • Dicken's avatar
        Dicken
        Post Prodigy
        4 months ago

        Regardign MS documentation.

        Taken from co pilot, so hope allowed; 

        Where the “Format record” confusion comes from

        Power Query does have functions that accept a record with [Format=…, Culture=…], but they are:

        • Value.FromText

        • Number.FromText

        • DateTime.FromText (in some host environments)

        • DateTimeZone.FromText (in some host environments)

        But not Date.FromText.

        This is why the documentation feels inconsistent — because it is.