My Journey with Power BI

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: ...

Recently I have created a relational DB in SQL and decided to share how I've done it. It's a snowflake design. Creating a relational DB is not difficult, but it's very difficult to change things after creating them. That's why we need to be very neat in the beginning, First I'd like to show you how it looks. For representation, I've used EXCEL so that I can visualize the tables by using color codes. Every color represents one relation. While ID columns are primary keys ...ID columns are foreign keys at other tables. And below you can see how the tables look after entering data. My first rule while I create a database is adding an ID column for every table and making it primary key and identity(1,1) so that it will increase automatically. The first thing we need for a DB table is a column that defines every row uniquely.  The second thing I try not to do is using dedicated words like 'PASSWORD' or 'USERNAME' for column names. I simply add undersco...

This is the first time I've created fake data using SQL, and some steps were quite challenging. That's why I've decided to write a post about it. The mission is to create a table with fake data, using random values. Below is the empty Customers table that I need to fill. Of course, other than the ID column, which is the primary key. First of all, I need names with gender info and surnames to fill the table. But don't get me wrong, I'm not going to insert those names directly to this table, because I need names and surnames to match randomly. For that reason, I've downloaded a name list with genders and a surname list from  www.randomlists.com . I also needed a list of USA states. I created one more table for that. This is how those tables look. In Names table I have 300 female names and 300 male names. In Surnames Table I have 600 surnames. You might be wondering why I put them to different tables. They have to be at different tables because if they are at the s...

If you know nothing about Statistics, you've come to the right place. I'm not going to bore you with long technical definitions that make no sense to people who don't know anything about it. I'm going to show everything with examples and visuals. You just sit back and relax. I have a Customers table. When I search for 'Abigail Parks' I get 5 records. And I have 3 indexes for this table. First is the clustered index, which is created automatically for the primary key column. The second and third ones are non-clustered indexes, one for the NameSurname column and the other one is for the Birthdate column. Now I'm wondering which index SQL used for my query. Was it able to find NameSurname index? When I click on Display Estimated Execution Plan button I get this: SQL was able to find the right index. In my next query, I try to find how many customers were born on the same day with the first 'Abigail Parks' on my list, which is 1973-06-05. I have 512 peop...

This post is about how to perform left join and right join in DAX, and in Power Query. If you need to refresh your memory about join types, you can read my post about it  here .  NATURALLEFTOUTERJOIN is used to perform left join in DAX. Left join means combining two tables by taking all the rows from left table and only the matching ones from right table. Microsoft's definition for this function is exactly the same as their definition for NATURALINNERJOIN meaning somebody made a wrong copy-paste. Here comes their links: https://docs.microsoft.com/en-us/dax/naturalleftouterjoin-function-dax https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax The syntax is pretty simple: NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>) Like I always do, I am going to explain it over an example. These are my tables that I'm planning the join.  This is the formula to join them and the error message comes with it. Like any other join function in DAX, it requir...

This post is about how to perform inner join in DAX. If you don't know what that is you, can access my previous post  here  which is about join types. Microsoft's definition for NATURALINNERJOIN is: " Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned." And the syntax is: NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)   First of all the definition is wrong. I had two tables with no common column names (check the image below) and I got no error. This definition is really misleading because it makes you think that Power BI creates connection between columns by using common column names but that's not what happens. You need to have a relationship between 2 tables to perform inner join. This Microsoft definition definitely needs correction. These are my tables and below is the formula with the result table. As you...

I have been thinking about writing a post about joins for a long time and the day has finally come. In this post, I am going to introduce the join types. In my upcoming post, we are going to learn how to join tables in DAX and in Power Query. Not to complicate things unnecessarily, I am going to keep definitions as simple as possible and use lots of visuals. Let's start then. Left Outer Join : It returns all the rows from the left table and the matched rows from the right table. It's also called left join. If you come up with any of the names, be aware that they are the same thing. Right Outer Join: Returns all the rows from the right table and the matched rows from the left table. It's also known as right join. Full Outer Join: It returns all the rows from both tables whether they are matched or not. Since it takes all rows, it can return very large result sets. Inner Join: This one is also known as Inner Join :) It takes rows that have matching values on both tables. Left...