{"id":2809,"date":"2024-06-03T18:24:02","date_gmt":"2024-06-03T23:24:02","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=2809"},"modified":"2024-06-05T22:34:10","modified_gmt":"2024-06-06T03:34:10","slug":"how-to-import-dates-correctly-across-culture-or-locales","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2024\/06\/03\/how-to-import-dates-correctly-across-culture-or-locales\/","title":{"rendered":"How to  Import Dates Correctly Across Culture  or Locales"},"content":{"rendered":"\n<p>Have you written a report where dates import right. But they break when ran on another machine?  If you import right, you don&#8217;t have to set cultures in your report settings. <\/p>\n\n\n\n\n\n<h2>This Is Caused by Default Cultures<\/h2>\n\n\n\n<p>When you import columns with <a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/table-transformcolumntypes\">Table.TransformColumnTypes<\/a>, the UI doesn&#8217;t include the culture parameter. <\/p>\n\n\n\n<figure class=\"wp-block-image alignwide size-large\"><img loading=\"lazy\" width=\"1012\" height=\"526\" src=\"http:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Pq-\u205e-Scene-1-Transform-Column-Types-\u205e-2024-05-\u205e-High-quality-low-color-\u205e-i0.gif\" alt=\"\" class=\"wp-image-2823\"\/><figcaption>Transform without culture<\/figcaption><\/figure>\n\n\n\n<p>If you pick Date and &#8220;Using Locale\u2026&#8221; it will replace this<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-powerquery\" data-line=\"\">\/\/ replace this\n= Table.TransformColumnTypes( Source,{  {&quot;Date&quot;, type text}} )\n\/\/ with this\n= Table.TransformColumnTypes( Source,{  {&quot;Date&quot;, type text}}, &quot;en-GB&quot; )<\/code><\/pre>\n\n\n\n<p>Now I can import a CSV that uses the culture &#8220;English UK&#8221; or &#8220;German&#8221; or others<br>And it<strong> still works when it&#8217;s ran on an &#8220;English-US&#8221; environment<\/strong><\/p>\n\n\n\n<h2>When is Culture \/ Locale used? <\/h2>\n\n\n\n<p><strong>Every time <\/strong>you convert dates and numbers <em>to text<\/em>, <strong>culture <\/strong>is involved. When you convert from text to numeric values, culture is involved.<br>If you don&#8217;t set the <strong>culture <\/strong>parameter, it falls back on the value <a href=\"https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/culture-current\">Culture.Current<\/a> . Mine uses &#8220;en-us&#8221;<\/p>\n\n\n\n<h2>Sample data to break things<\/h2>\n\n\n\n<p>Here&#8217;s a variety of dates to compare which breaks. They came from  cultures using the named <strong>ShortDate &#8220;d&#8221;<\/strong> format string<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Article-Image.2024-05-29.png\" alt=\"\" class=\"wp-image-2819\" width=\"697\" height=\"340\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Article-Image.2024-05-29.png 697w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2024\/06\/Blog-Post-Article-Image.2024-05-29-300x146.png 300w\" sizes=\"(max-width: 697px) 100vw, 697px\" \/><\/figure><\/div>\n\n\n\n<h2>Related Links<\/h2>\n\n\n\n<ul><li>With <a href=\"https:\/\/github.com\/ninmonkey\/Ninmonkey.PowerQueryLib\/tree\/febbb554cdbfb2a3ad41e69c21472f1998f539d7\/Examples\/Export-Culture\">Nin.PqLib for powershell<\/a> it&#8217;s easy to export named format strings for all cultures<\/li><li><a href=\"https:\/\/learn.microsoft.com\/en-us\/dotnet\/standard\/base-types\/standard-date-and-time-format-strings#table-of-format-specifiers\" data-type=\"URL\" data-id=\"https:\/\/learn.microsoft.com\/en-us\/dotnet\/standard\/base-types\/standard-date-and-time-format-strings#table-of-format-specifiers\">Listing of Named Date Format Strings<\/a> &#8211; Microsoft docs<\/li><\/ul>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Have you written a report where dates import right. But they break when ran on another machine?  If you import right, you don&#8217;t have to set cultures in your report settings. <\/p>\n","protected":false},"author":1,"featured_media":2824,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[155,85,84,6,7,154],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2809"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=2809"}],"version-history":[{"count":19,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2809\/revisions"}],"predecessor-version":[{"id":2953,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/2809\/revisions\/2953"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/2824"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=2809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=2809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=2809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}