Let’s think about this scenario. In the first encounter of Power Query, you were presented with demonstrations that show all the powerful transformations done by Power Query. You were surprised and amazed with the demos. Normally we did not pay attention to subtle details about the powerful tools. We jumped straight into all the transformation steps by following examples. Sometimes it works, something it doesn’t. And then you feel frustrated as you don’t know why some calculations give you unexpected results. If this sounds familiarize, I will say, this is because we are all trying to run before walk. 😅
In this blogpost, and in the video, I will talk about a basic but crucial step in Power Query – Data Type.
Data Type in Power Query is NOT formatting in Excel
Formatting does not change underlying values, defining data type in Power Query does.
When we work with data using Power Query, defining data type of each column before loading the result is a best practice. Please don’t mix it up with formatting in Excel. Defining data type to a column is fundamentally different from applying a formatting. In Excel, when we apply a format to a number, the underlying value of the number remains unchanged. On the contrary, when we define data type of a column in Power Query, all the values under the column will be converted to the data type that’s being defined.
Power Query is not behaving like Excel when doing calculation
Nevertheless, defining data type in Power Query is not for data conversion only. We need the data type to be explicitly defined before some calculations happen. Otherwise, we may have unexpected results returned. The worst scenario is we don’t even know that. For example, when you try to add “10” to 10, Excel returns 20 while Power Query returns error. Similarly, when you try to add 10 to an empty cell, Excel return 10 but Power Query returns null. In general, number does not work with text; text does not with number in Power Query.
Are you aware of this? Let’s watch it in action:
You may download a sample file to follow along:
I haven’t covered all the details about data types in the video. I would highly recommend you read the documentation from Microsoft if you want to learn more.
https://docs.microsoft.com/en-us/power-query/data-types
In the process of preparing the video, I found two “weird” behaviors of Power Query that puzzled me a lot:
- How it ROUND number by default
- How it treats dates before 1900/3/1 (indeed it has rectified a well-known issue of Excel)
Want to learn more? Please read the two great articles from MrExcel. Links below:
https://www.mrexcel.com/excel-tips/helpful-secrets-about-rounding-in-power-query/
https://www.mrexcel.com/excel-tips/early-dates-in-power-query-are-off-by-a-day/
I learned so much from MrExcel. After reading the two articles, I feel even more comfortable in using Power Query because I know that the engineers behind the scenes are so attentive to details.
If you just start to learn Power Query, my two cents would be starting from the basic. It will pave the path for you to go farther for sure.
wmfexcel 