Friday, September 18, 2015

Data type Changes when loading Power BI Data

During the data loading process, Power BI Desktop evaluates the incoming data, and then assigns what it thinks is the correct data type. And in most cases, it makes a good choice. But what if it makes the wrong choice? 

For example, what if one of the columns is a text field made up of the month and the year? Such as December, 2015. Power BI Desktop decides that this is a data field, and changes the value to 12/1/2015. Great if that is what you want.

Editing the data types


During the initial load process, one of the first screens will be the Navigator. On the bottom right, select/click on the Edit button.




This will bring up the Query Editor. The Query Editor is one of the most powerful tools available to the Power BI Desktop, Over time, you'll find this tool more and more useful. On the right side of the Query Editor, locate the Applied Steps section. For this example, the last step the query editor performed was the Change Type.



Simply right click on the Change Type item, and select Delete. Or, simply select/click on the X immediately to the left of the Change Type item. Once the change has been made, the Sales Month column is now back to the value we wanted.




While we are in the Query Editor, lets update the data type for the monetary columns. Holding the Ctrl button, select/click on the monetary columns. On the Data Type combo box, select the Fixed Decimal Number value. 



For our example, the incoming column in Excel was configured as Currency. Once we change the Data Type to Fixed Decimal Number, Power BI recognizes the columns as Currency.


  
When you are finished, select/click the Close & Load button. After loading the data, you can review using the Data Tools. The currency columns are now presented as currency values.





No comments: