{"id":1873,"date":"2021-01-13T14:21:51","date_gmt":"2021-01-13T14:21:51","guid":{"rendered":"https:\/\/bi.unija.com\/en\/?p=1873"},"modified":"2021-01-13T14:22:52","modified_gmt":"2021-01-13T14:22:52","slug":"r-and-power-bi-dates","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/","title":{"rendered":"R and Power BI \u2013 Dates"},"content":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019).<\/em><\/p>\n

Working with dates with Power BI requires an extra steps, since R doesn’t recognize date data automatically. Let’s look at the following example. We have a table with columns Date<\/strong> and Price<\/strong>. Column Date<\/strong> is of type date.<\/p>\n

<\/p>\n

We use R to add column Price2<\/strong>, which is column Price<\/strong> multiplied by 2. We take the following steps.<\/p>\n

Open Transform ><\/strong> Run<\/strong> R<\/strong> script<\/strong>.<\/p>\n

<\/p>\n

Enter the following:<\/p>\n

newname = dataset<\/p>\n

newname[‘Price2’] = newname[‘Price’]*2<\/p>\n

<\/p>\n

First row creates a new variable newname, duplicate of variable dataset<\/strong>. We can’t change dataset<\/strong> variable, so we always create a duplicate.<\/p>\n

Second row adds column Price2<\/strong> to table newname<\/strong>, a product of column Price<\/strong> and 2.<\/p>\n

Confirm with OK<\/strong> and newname <\/strong>table is created.<\/p>\n

<\/p>\n

We see R doesn’t recognize date data<\/strong>. For advanced manipulation of data we would need to use one of many available packages. But for simple problems we can just change data type to number. Let’s look at both examples.<\/p>\n

Example: change date column to text<\/h1>\n

We start the same as in the example above.<\/p>\n

<\/p>\n

We take the following steps.<\/p>\n

Change the date column into text<\/strong>.<\/p>\n

<\/p>\n

We can recognize text by left justification.<\/p>\n

<\/p>\n

Continue the same as in example above. Add column Price2<\/strong> which is a product of column Price<\/strong> and 2<\/strong>. Select Transform<\/strong> > Run<\/strong> R<\/strong> script<\/strong>.<\/p>\n

<\/p>\n

Enter following commands.<\/p>\n

newname = dataset<\/p>\n

newname[‘Price2’] = newname[‘Price’]*2<\/p>\n

<\/p>\n

Confirm with OK<\/strong>. We get a new table, but the date is still treated as text.<\/p>\n

<\/p>\n

Change data type of column Date<\/strong> to date.<\/p>\n

<\/p>\n

Example: calculating monthly average<\/h1>\n

Example above is a quick solution that can work in simple queries. But often we want to actually calculate using date data. Let’s look at the following example.<\/p>\n

We want to calculate monthly average using the same table.<\/p>\n

<\/p>\n

We take the following steps.<\/p>\n

Change date column to text.<\/p>\n

<\/p>\n

We can recognize text by left justification.<\/p>\n

<\/p>\n

Select Transform<\/strong> > Run<\/strong> R<\/strong> script<\/strong>.<\/p>\n

<\/p>\n

Enter the following commands.<\/p>\n

# ‘dataset’ holds the input data for this script<\/p>\n

newname = dataset<\/p>\n

newname$Date = as.Date(newname$Date, format = “%m\/%d\/%Y”)<\/p>\n

newname$Month = format(newname$Date, format = “%m”)<\/p>\n

newname$Year = format(newname$Date, format = “%Y”)<\/p>\n

newname = aggregate(Price ~ Month + Year, newname, mean)<\/p>\n

<\/p>\n

Confirm with OK<\/strong>. We get monthly averages of prices.<\/p>\n

<\/p>\n

We can get the same result via multiple other ways, for example using one of various date packages.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019). Working with dates with Power BI requires an extra steps, since R doesn’t recognize date data automatically. Let’s look at the following example. We have a table with columns Date and Price. Column<\/p>\n","protected":false},"author":5,"featured_media":1891,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[10],"class_list":["post-1873","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-intermediate","tag-r"],"acf":[],"yoast_head":"\nR and Power BI \u2013 Dates - Bi Unija<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"R and Power BI \u2013 Dates - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"For Power BI version 2.76.5678.782 (December 2019). Working with dates with Power BI requires an extra steps, since R doesn’t recognize date data automatically. Let’s look at the following example. We have a table with columns Date and Price. Column\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2021-01-13T14:21:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-13T14:22:52+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/\",\"url\":\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/\",\"name\":\"R and Power BI \u2013 Dates - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png\",\"datePublished\":\"2021-01-13T14:21:51+00:00\",\"dateModified\":\"2021-01-13T14:22:52+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"R and Power BI \u2013 Dates\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"R and Power BI \u2013 Dates - Bi Unija","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/","og_locale":"en_US","og_type":"article","og_title":"R and Power BI \u2013 Dates - Bi Unija","og_description":"For Power BI version 2.76.5678.782 (December 2019). Working with dates with Power BI requires an extra steps, since R doesn’t recognize date data automatically. Let’s look at the following example. We have a table with columns Date and Price. Column","og_url":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/","og_site_name":"Bi Unija","article_published_time":"2021-01-13T14:21:51+00:00","article_modified_time":"2021-01-13T14:22:52+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png","type":"image\/png"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/","url":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/","name":"R and Power BI \u2013 Dates - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png","datePublished":"2021-01-13T14:21:51+00:00","dateModified":"2021-01-13T14:22:52+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/01\/40.png","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/r-and-power-bi-dates\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"R and Power BI \u2013 Dates"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1873"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1873"}],"version-history":[{"count":1,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1873\/revisions"}],"predecessor-version":[{"id":1892,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1873\/revisions\/1892"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1891"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}