{"id":32360,"date":"2021-01-16T10:30:22","date_gmt":"2021-01-16T10:30:22","guid":{"rendered":"https:\/\/www.testpreptraining.com\/tutorial\/?page_id=32360"},"modified":"2021-01-16T10:34:32","modified_gmt":"2021-01-16T10:34:32","slug":"evaluating-and-transforming-column-data-types","status":"publish","type":"page","link":"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/","title":{"rendered":"Evaluating and Transforming Column Data Types"},"content":{"rendered":"\n<p>As you may know, that when a table is imported from any data source, the Power BI Desktop automatically starts scanning the first 1,000 rows (default setting). Thereby it tries to detect the type of data in the columns. But there are situations when Power BI Desktop does not detect the correct data type. However, whenever incorrect data types occur, you will encounter performance issues.<\/p>\n\n\n\n<p>Moreover, there is a higher chance of getting data type errors when you are dealing with flat files, like comma-separated values (.CSV) files and Excel workbooks (.XLSX). Since data entered manually into the worksheets could lead to mistakes. On the other hand, in databases, the data types are predefined when tables or views are created.<\/p>\n\n\n\n<p>One of the best practice for you is to evaluate the column data types in Power Query Editor before loading the data into a Power BI data model. So, in case you discover that a data type is incorrect, you can change it. Also, you may wish to apply a format to the values in a column and change the summarization default for a column.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"implications-of-incorrect-data-types\"><strong>Effects of Incorrect Data Types<\/strong><\/h4>\n\n\n\n<p>Some of the issues that can arise when Power BI does not detect the correct data type are &#8211;<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Firstly, Incorrect data types will prevent you from creating certain calculations, deriving hierarchies, or creating proper relationships with other tables. <\/li><li>Secondly, one of the issues with having an incorrect data type applied on a date field. It is the inability to create a date hierarchy, which would allow you to analyze your data on yearly, monthly, or weekly basis. But, it is considered best to use a date table and turn off the auto date\/time to get rid of the auto-generated hierarchy. <\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"change-the-column-data-type\"><strong>How to Change the column data type?<\/strong><\/h3>\n\n\n\n<p>There are two options for you to change the data type of a column &#8211; Power Query Editor and Power BI Desktop Report view by using the column tools. Also, it is considered best to change the data type in the Power Query Editor before you load the data.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"change-the-column-data-type-in-power-query-editor\"><strong>FIRST METHOD &#8211; Changing the column data type in Power Query Editor<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\"><li>Firstly, in Power Query Editor, you can change the column data type by selecting the column that has the issue. Then select\u00a0<strong>Data Type<\/strong>\u00a0in the\u00a0<strong>Transform<\/strong>\u00a0tab, and select the correct data type from the list.<\/li><li>Now, the other method is to select the data type icon next to the column header and then select the correct data type from the list.<\/li><\/ul>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"750\" height=\"274\" src=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/image-9-750x274.png\" alt=\"\" class=\"wp-image-32369\" srcset=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/image-9-750x274.png 750w, https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/image-9.png 1055w\" sizes=\"auto, (max-width: 750px) 100vw, 750px\" \/><figcaption>Source: Microsoft<\/figcaption><\/figure><\/div>\n\n\n\n<ul class=\"wp-block-list\"><li>Once you make the change in Power Query Editor, in the column data type is saved as a programmed step. This step is referred to as <strong>Changed Type<\/strong>&nbsp;which will be iterated each time you refresh the data.<\/li><li>Lastly, when you have completed all steps to clean and transform your data, select&nbsp;<strong>Close &amp; Apply<\/strong>&nbsp;to close Power Query Editor to apply the changes to your data model. <\/li><\/ul>\n\n\n\n<p>Once done, your data will be in well organized and arranged for analysis and reporting.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>SECOND METHOD &#8211; Changing the column data type in Power BI Desktop<\/strong><\/h4>\n\n\n\n<p>In Power BI Desktop, when you load data, it attempts to convert the data type of the source column. Further the data type should supports more efficient storage, calculations, and data visualization. now, in Power BI Desktop, you can ascertain and define  a column\u2019s data type in the Query Editor, or in Data View or Report View.<\/p>\n\n\n\n<p>For more visit: <a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/desktop-data-types\" target=\"_blank\" rel=\"noreferrer noopener\">Power BI Desktop Data Types<\/a><\/p>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-large\"><a href=\"https:\/\/www.testpreptraining.ai\/analyzing-data-with-microsoft-power-bi-da-100-free-test\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" width=\"961\" height=\"150\" src=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/Microsoft-Exam-DA-100-Free-Practice-Test.png\" alt=\"Microsoft Exam DA-100 Free Practice Test\" class=\"wp-image-31483\" srcset=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/Microsoft-Exam-DA-100-Free-Practice-Test.png 961w, https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/Microsoft-Exam-DA-100-Free-Practice-Test-750x117.png 750w\" sizes=\"auto, (max-width: 961px) 100vw, 961px\" \/><\/a><\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"<p>As you may know, that when a table is imported from any data source, the Power BI Desktop automatically starts scanning the first 1,000 rows (default setting). Thereby it tries to detect the type of data in the columns. But there are situations when Power BI Desktop does not detect the correct data type. However,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"footnotes":""},"categories":[174],"tags":[1830,3954],"class_list":["post-32360","page","type-page","status-publish","hentry","category-microsoft-azure","tag-da-100-study-guide","tag-microsoft-exam-da-100-free-practice-test"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Evaluating and Transforming Column Data Types | Testprep<\/title>\n<meta name=\"description\" content=\"Boost your learning and get ready to qualify Microsoft Exam DA-100 with hundreds of practice exam. Learn more about Column Data Types Now!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Evaluating and Transforming Column Data Types | Testprep\" \/>\n<meta property=\"og:description\" content=\"Boost your learning and get ready to qualify Microsoft Exam DA-100 with hundreds of practice exam. Learn more about Column Data Types Now!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/\" \/>\n<meta property=\"og:site_name\" content=\"Testprep Training Tutorials\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-16T10:34:32+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/image-9-750x274.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/\",\"name\":\"Evaluating and Transforming Column Data Types | Testprep\",\"isPartOf\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\"},\"datePublished\":\"2021-01-16T10:30:22+00:00\",\"dateModified\":\"2021-01-16T10:34:32+00:00\",\"description\":\"Boost your learning and get ready to qualify Microsoft Exam DA-100 with hundreds of practice exam. Learn more about Column Data Types Now!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Evaluating and Transforming Column Data Types\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#website\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"name\":\"Testprep Training Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#organization\",\"name\":\"Testprep Training\",\"url\":\"https:\/\/www.testpreptraining.ai\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"contentUrl\":\"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png\",\"width\":583,\"height\":153,\"caption\":\"Testprep Training\"},\"image\":{\"@id\":\"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Evaluating and Transforming Column Data Types | Testprep","description":"Boost your learning and get ready to qualify Microsoft Exam DA-100 with hundreds of practice exam. Learn more about Column Data Types Now!","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:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/","og_locale":"en_US","og_type":"article","og_title":"Evaluating and Transforming Column Data Types | Testprep","og_description":"Boost your learning and get ready to qualify Microsoft Exam DA-100 with hundreds of practice exam. Learn more about Column Data Types Now!","og_url":"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/","og_site_name":"Testprep Training Tutorials","article_modified_time":"2021-01-16T10:34:32+00:00","og_image":[{"url":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-content\/uploads\/2021\/01\/image-9-750x274.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/","url":"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/","name":"Evaluating and Transforming Column Data Types | Testprep","isPartOf":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website"},"datePublished":"2021-01-16T10:30:22+00:00","dateModified":"2021-01-16T10:34:32+00:00","description":"Boost your learning and get ready to qualify Microsoft Exam DA-100 with hundreds of practice exam. Learn more about Column Data Types Now!","breadcrumb":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/evaluating-and-transforming-column-data-types\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.testpreptraining.ai\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Evaluating and Transforming Column Data Types"}]},{"@type":"WebSite","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#website","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","name":"Testprep Training Tutorials","description":"","publisher":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.testpreptraining.ai\/tutorial\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#organization","name":"Testprep Training","url":"https:\/\/www.testpreptraining.ai\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","contentUrl":"https:\/\/www.testpreptraining.com\/tutorial\/wp-content\/uploads\/2020\/07\/tpt-logo-6.png","width":583,"height":153,"caption":"Testprep Training"},"image":{"@id":"https:\/\/www.testpreptraining.ai\/tutorial\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/32360","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/comments?post=32360"}],"version-history":[{"count":6,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/32360\/revisions"}],"predecessor-version":[{"id":32370,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/pages\/32360\/revisions\/32370"}],"wp:attachment":[{"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/media?parent=32360"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/categories?post=32360"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.testpreptraining.ai\/tutorial\/wp-json\/wp\/v2\/tags?post=32360"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}