Skip to main content

Solving Data Type Conflicts in Power BI and SQL

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about.


The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print  "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: There can't be data with different data-types in one column. I get the error because "First Day"'s data type is string, whereas the data type of First Sale Date column is date. If the data type of my Sales Day column is going to be text, then it can't show date values, if it's going to be date, then it can't show text value. In this situation, I have no other choice but to choose a data type that can show both values. How am I going to do it then?


This is how it's solved. The data type that can show both date and text is text. What I've done in the formula is: 1. Convert the data types of Registration Date and First Sale Date columns from date to text 2. If Registration Date and First Sale Date are the same, print  "First Day", otherwise show the data of First Sale Date column. It was a successful operation and this time my formula worked.

My next question is how can we do it in SQL? Actually, we have three different ways to do it in SQL by using FORMAT, CAST and CONVERT functions. Below is the code which is not going to work.
UPDATE
       CUSTOMERS
       SET SalesDay =
       (CASE WHEN RegistrationDate = FirstSaleDay
       THEN 'First Day'
       ELSE FirstSaleDay
       END)

And this is SQL'S way of saying that tere can't be data with different data-types in one column.




Both error messages look different from each other but they are complaining about exactly the same issue. 

To enter data in SalesDayFORMAT, SalesDayCONVERT, and SalesDayCAST columns, I've used these formulas:

UPDATE
       CUSTOMERS
       SET SalesDayFORMAT =
       (CASE WHEN FORMAT(RegistrationDate, 'yyyy-MM-dd')  = FORMAT(FirstSaleDay, 'yyyy-MM-dd') 
       THEN 'First Day'
       ELSE FORMAT(FirstSaleDay, 'yyyy-MM-dd')
       END
UPDATE
       CUSTOMERS
       SET SalesDayCONVERT =
       (CASE WHEN CONVERT(VARCHAR,RegistrationDate, 121)  = CONVERT(VARCHAR,FirstSaleDay, 121) 
       THEN 'First Day'
       ELSE CONVERT(VARCHAR, FirstSaleDay, 121)
       END)
UPDATE
       CUSTOMERS
       SET SalesDayCAST =
       (CASE WHEN CAST(RegistrationDate AS VARCHAR)  = CAST(FirstSaleDay AS VARCHAR) 
       THEN 'First Day'
       ELSE CAST(FirstSaleDay AS VARCHAR)
       END)

All of them give the same result but which one should we prefer? Well, Microsoft says: 'Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.' Format function offers more flexibility than others. While CONVERT and FORMAT accept an optional style parameter, CAST doesn't give that option. What makes CAST preferable is it's easier to read.

That's it. These are our options to solve the problem. I don't know you, but I would stick with Microsoft's advice and use FORMAT if I had faced the issue in SQL. What would you do? If you have any other idea or feel like sharing what do you think, please comment below.

Labels:

Comments

Post a Comment

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...
Post a Comment
Read more

Customizing Themes With Advanced Elements in Power BI

Advanced Elements is not an intuitive description, that's why I'm going to show you what I'm talking about directly. For my recent project, I've decided to create my own theme for the report. When I was exploring Customize Theme tab, I have come up with the Advanced section. While text, visuals, page, and filter pane sections are self-explanatory, this advanced section seemed quite enigmatic to me. What are the first-level elements? Or the others? I think Microsoft agrees with me upon being enigmatic because the section comes with "Learn more" option: Click to  Learn More . But when I tried to learn more, it didn't make any sense to me, because I'm a visual person and those descriptions are quite hard to remember. That's why I have decided to be a good samaritan and visualize it, and then share it with you.  As you see, while the border is a third-level element, the shadow is a first-level element. Legend, x-axis, and y-axis are second-level elemen...
Post a Comment
Read more

Calculating Running Total with Quick Measure in DAX

It's been a while since quick measures were introduced and it might be tricky to decide whether to use them or not. What I have noticed recently is calculating running total with quick measure might not provide the best user experience because it simply doesn't filter explicitly, despite ALLSELECTED function is part of the measure.     This is the quick measure to calculate the running total and it can be seen that ALLSELECTED function is part of the equation. As a result, I got the impression that when I explicitly filter, the measure will retain all the explicit filters but that’s not what happened. This is how it looks like before I use any explicit filter.   Everything looks perfect right. What until I filter 2017 and 2018.     As you see two numbers don't match because quick measure adds up sales of 2016 despite I didn't select 2016 on year slicer and despite quick measure uses ALLSELECTED function. This is the result we were supposed to get if ALL function...
Post a Comment
Read more