Analyst in Action

You must be aware of the purpose and significance of Vlookup in Excel. But when it comes to Microsoft Power BI there is no Vlookup in it. Power BI provides you Related and Lookupvalue which is quite similar to Vlookup in Excel. If you aren't familiar with the Vlookup kindly refer to our blog . Let's get started with the purpose of both functions. You will be shocked to know that both of them will give you the same result. Because it follows the same principle of Vlookup i.e. searching for a particular value in a column and returns a value from a different column (different table). In this blog, we will showcase how and when to use related and lookup values. We will be using Sample Superstore data. The question that comes to my mind is when to use the related functions? So there are certain criteria to be met before creating a column with related. One of the conditions is that both the tables (one where we are creating a column and the other will be from where the value will com...

Let's discuss another problem of the week. As a Power BI user, there are times when you want to combine queries. What are the ways to do so? In most cases, you can attain it by using either append or merge and both serve different purposes. Let's understand what do these terms mean in Power BI and how they are functionally different from each other.  It is quite common to get data from various sources and you need to combine those data depending on a particular column which is common in both tables so that you can add extra information or column to your big table. In such cases, we use merge queries. How to perform merge queries? For instance, I am considering Sample Superstore data and we will merge the returns table to the order table. You will find both merge and append in the home tab in extreme right in the power query editor. ProTip - You will find two options when you click on the drop-down in merge which are merge queries and merge queries as new. When you use merge que...

Finally, we are back with new blogs. The idea of all the blogs is to share the problem of the week which I faced and provide a solution to it. So, as Business Intelligence Analyst one of my major responsibilities is to design an optimized data model and avoid many to many relationships. The primitive approach to such a problem is to create bridge tables out of a big flat table and create one to many relationships in that process. Creating bridge tables can be achieved in the Power Query Editor. There are mainly two ways to achieve that one is to take reference tables and the other is to create a duplicate table out of the big flat table. So what is the difference between duplicate and reference? Let's dig deeper into it. If you see both of them create a copy of the main table but in duplicate, it will copy the changes applied to the main table whilst in the reference the bridge table will be isolated from all the changes applied to the main table. The reference query always points ...

In today's blog, we will deep dive into the Power query editor. The interface of the power query editor is a replica of an excel spreadsheet but in the former things are much easier as compared to excel. Power query editor offers a lot of features which we have discussed in the guide to power query editor blog .  In this blog, we will focus on one of the features i.e. add columns. You can find it next to the transform tab in the power query editor. We will be working with the sample superstore data. The idea is to explore the add column tab. Let's start with duplicating the column which is just a click away feature available in the power query editor. You just need to select the column of which you need to make a duplicate and select the duplicate column tab available on the top. Unlike in Excel, you need to apply shortcuts to achieve the same. The power query editor is all about making your life easier. From duplicating the columns we will move to index the columns which can b...

What if I told you there's no need to write laborious DAX expressions in Power BI? Doesn't that sounds great? I personally enjoy writing the DAX expressions from scratch because it helps to get to know the data well and you can tailor your calculations. But if you don't like writing the DAX expressions from square 1 then you should consider "Quick Measures". Power BI always makes your task easy in visualizing your data and quick measures are something that helps you to create measures in few seconds. Doesn't that sound amazing? Yeah!! As the name suggests creating measures just by dragging and dropping is an accentuating feature of quick measures. I consider it to be the future of measures in Power BI. In this blog, I will showcase step by step process to create quick measures. I will be working on the Sample Superstore data. You can find the quick measures right next to the new measure on the top or else you can right-click any of the available table options ...

Are you spending a big chunk of your time cleaning the data in excel? If yes then this blog is for you. All the cleaning and transforming of data can be achieved in Microsoft Power BI. Power BI offers a wide variety of options to clean your data. Power BI comes with a power query editor where you can play around with the data and I consider it as an ETL tool where you can transform your data according to the needs. We have discussed the data cleaning process in the last blog and in today's blog the focus will be on transforming data and the features that the power query editor offers. These steps are solely subjective according to the data type and to your needs. I am highlighting 6 steps to transforming the data. I am working on the sample superstore data. So let's get started!!!! Removing columns and rows-  We are aware that the datasets can have a lot of unwanted columns and rows. To get better insights you need to get rid of it. To do so you need to select the column. If y...