Top 10 Power BI Interview Questions (based on what I usually ask)

are you ready to unlock your dream job in the world of data analytics and boost your earnings potential well you have come to the right place in today's video I'm going to talk about the top 10 powerbi interview questions so that you can transform your career but there is a Twist a literal twist I got here a spin wheel app and I'm going to spin this and take up these questions in the random order you don't want to miss the ending for an ultimate surprise so let's go for a spin okay so the first one is going to be number two can you explain the difference between a measure and a calculated column now a measure is something that we can use to calculate things on top of a table so for example if I have got some shipment data in a table and I just want to count how many shipments are there we can use a measure like count rows on top of the shipments table to count the number of shipments whereas a calculated column is something that is part of the table so in the shipments table we can use a calculated column to calculate the discount rate based on the order quantity of that shipment one simple way to think about about these is if you think your data table as this hand then a measure is something that is outside this hand so it acts on top of the hand but it's not part of the hand whereas a calculated column is like one of the fingers in your hand it is part of the hand itself so that is the main difference between these two things this is a lot harder than I imagined [Music] so the next one is question number seven describe the process of publishing a powerbi report to the powerbi service so the process begins like this once you finished creating the report and testing it and sure that you're happy with the results then you save the file and you hit the publish button in the powerbi desktop this is going to take your report as well as the data set and then publish it to online platform in a workspace that you select from that point onwards your report is available for anybody who has access to that workspace so this is why it is important to properly test your reports before you publish them because once it is out anyone in your organization who can get to that workspace can see what you have created so you don't want to have any mistakes the process also involves selecting the the workspace and making sure that the right people have access to that workspace that sort of a thing can be set up in the powerbi service Itself by either you or your it [Music] administrator the next one is number 10 how would you handle missing data in powerbi so when you get questions like this it's a good idea to not just give textbook style answers it is also important and probably more valuable if you give answers from your own previous experiences either in work or life so I'm going to give you an example from when I had to deal with missing data recently in a powerbi project we were looking at data that is collected from external parties now we sent out a survey asking external parties to fill out the survey and then we are analyzing it naturally as we don't have control over this data there were many missing values and the strategy that we used is multifold for certain missing values as they're not too important for our analysis we choose to ignore them so we didn't bother analyzing or considering those averages or values when calculating summaries for other kinds of missing data we choose to again go back to the source and ask them again if they can provide these missing values so that is another strategy that I would use the third option is something that I also used because certain missing values we couldn't go back and ask them so this is where I used a technique called imputation so let's say we have got some values and there is one missing value in the middle somewhere we can impute that missing value by for example calculating the average of the overall column excluding those missing values and then using that average as the missing value we could also use median mode or any other statistical metric there power query which is the main place where your data comes into powerbi offers many ways to handle these missing values it also helpfully identifies if there is a missing value in your data and the next one is question number five what is Dax and can you give me an example of a recent Dax formula you have used Dax stands for data analysis Expressions it is the main calculation language of powerbi using tax we can calculate measures we can calculate columns we can even calculate tables as part of the powerbi a recent example that I used is similar to what I mentioned earlier the shipment count measure so we have got a shipments table and I wanted to know how many shipments are done so I used a count Rose measure on the shipments table to count how many shipments are there and then I took this measure and sliced it by geography so that I can see how many shipments are going to India or New Zealand or Australia some of the geographies in which my previous company operated a more advanced version of the same shipment count measure is low box shipment count this is where sometimes we send shipments with very few boxes of chocolate in them did I mention I used to work in a chocolate company so I wanted to count how many times we are shipping where the Box count is less than 50 so I used a Dax formula on shipment count along with calculate formula to introduce an extra filter to calculate how many low box shipments are there and then I again was able to visualize this by product or by salesperson to understand who is sending more low boox shipments while I'm going to keep my finger busy by tapping this pin button I want you to keep your finger busy by tapping that like button if you're enjoying this video so go ahead and do that the next one is number three describe the steps you would take to connect multiple sources to a powerbi report when you open powerbi and when you're trying to connect initially you can bring in one data source this could be a SQL Server table or an Excel file or a web page but once you already have that data in powerbi through Power query you can add any number of connections so we simply use par query and then make a new connection once all the data is there from different systems I can then use power query to either merge the tables or append them or combine the tables to create a third composite table and load that into powerbi the next one is number one what are the main differences between powerbi desktop and powerbi service powerbi desktop is the main software or tool that I use as a data analyst to create the reports or calculate things or establish connections and clean my data once I finish my job I then use powerbi service to publish and share the reports with other people in my organization or outside the organization so the key difference between these two things is one is a creation tool the desktop application is the creation tool and the service is the publishing tool so consumption tool anybody who is usually on the service their main intention is going to be consume the data or reports that you have published understand what is going on and then take actions all right we are at the last four now let's see what this one brings up number six how do you optimize the performance in powerbi reports again I want to remind you that when you get questions like this instead of giving theoretical or you know researched answers give it from your experience like what you did when you saw that a report was slow so for example a recent report that I built for a client was all right initially but it was kind of getting slow over a period of time so here is what we did I first looked at the data that is being used in the report and I realized that even though we are only analyzing 2023 performance for some reason we are bringing data from 2021 and 2022 as well initially when when the report was created there was a need to have those but the client is no longer looking at those parts or they are no longer interested in those strengths so a simple thing that I did is to remove all the 2021 and 2022 data by updating the query part uh you could do this in either power query or in our case we actually modified the we condition in the SQL so that the data never came into Power query itself this drastically reduced the amount of data points that are coming into powerp and improveed the performance but let's just say that could not fix the problem you you still had to see all of this data then the next things that I would do is I would look at the page where the data is being presented and then I will ask questions about are there any unnecessary visuals or graphical elements that are not contributing to the overall analysis if so I would take them out because every little thing on the page is going to add a little bit of load time to the report even if that could not solve the problem then I will use a performance profiler our bi has a built-in profiler option so that I can run it and then measure for each of the elements on the page how much time they're taking both for query as well as rendering and then I will try to attack the items that have very high render values a simple technique although I've never used it previously is rather than loading the raw data you could also consider building some aggregate tables powerbi offers native way to do this I never done did that as there was no requirement for that but that option is also available okay only three more number eight now for this one I'm not going to answer the question simply because I I do know the answer it's just that I thought it'll be fun if you also get a chance to answer this question so I'll read out the question I'll give you a short answer but I want you to answer this in the comments below explain the RO level security in powerp how would you implement it so Ro level security is a feature of powerbi that we can use to restrict people from seeing parts of the data that they shouldn't be seeing so for example if you are a manager you can see everything but if you are a line employe you should only be seeing information that pertains to you so you can use Ro level security to do that now how would you answer this again use your life experiences either from work or personal projects that you have done and tell me in the comments how you would answer this interview question okay only two more number four and that makes number six as the last one or I think number nine as the last one yeah uh what does the one star and the arrow mean in the data model diagram So within powerbi when you have multiple tables you can create a data model in the data model will have these extra symbols between the tables on the line that connects them so a one and star means the relationship is one to many a simple example is going back to the shipment table that I was talking about in each shipment we will have a product code and a corresponding product Dimension table that tells us more about these products so the relationship between shipments and products table is many to one that means many shipments can be for the same product so the many side is usually denoted with star and one side is denoted with one and then there is also an arrow usually pointing from product table to the shipments table what this arrow means is the filters will flow from product table down to the shipments table so that Arrow indicates the propagation of filters uh a simple way to think about this is in a report if I have got a slicer on a product category and I select a specific category like bars in the product category slicer then because that slicer is in the product table my product table is going to be filtered first and as the arrow say is the filter should now propagate down to shipments it's going to come down to shipments table shrink the shipment table to just the selected categories records and then all the values on the screen will be calculated for that so that's what these symbols mean and now the last question is number nine which is what is a slowly changing Dimension SCD give me an example and how you handle it so again I'm going to partly answer this but I want you to think about it and use the comments to write your experience with SCD a slowly changing Dimension is any Dimension that slowly changes over time so a simple example is going back to the previous one my product table within the product table we have a option called Coco percentage so we sell chocolate so every product has a certain percent of cocoa in it and that percent is usually fix it for many of our products but some of the more experimental products we might tweak this over a period of time so initially a product might have 20% Coco but based on the customer feedback we might choose to reduce it down to 18% or increase it to 30% so that that column itself is it's not changing every day but it changes maybe once every 6 months or once a year depending on seasonal patterns and whatnot now when that happens that is a slowly changing Dimension because that aspect of that Dimension is changing slowly uh and you can handle this by keeping track of the percentages in another separate table or having separate rows every time such change happened with an effective date uh let's say you wanted to understand what impact that cocoa percentage had on customer satisfaction or shipment count or something else uh you you need to know what was the percentage before and after that slow change happened so we need to have those records in some other cases if it is a trivial thing we wouldn't bother keeping all the values we just update delete the old value and put the new value so that is an example of a slow changing Dimension how do you answer this again put your choices or options in the comments now let's talk about the surprise if you are thinking all of these questions are fine but I don't understand that well enough I don't have that technical understanding or the confidence then I have got good news for you I'm running my annual powerbi weekend this year on November 18th and 19th in this powerbi weekend it's a 4our event so 2 hours on Saturday 2 hours on Sunday we are going to meet in an online meeting and I will be teaching how to use powerbi so that you can gain that initial confidence and comfort level around this software it's happening on November 18th and 19th in 2023 but if you're watching this after that date you can still find the next iteration of this event details in the video description below so the tickets for this are on sale go ahead and sign up now if you sign up now you will get a powerbi cheat sheet as an extra bonus as well so go ahead and check that out powerbi weekend happening on November 18 and 19 now whether you choose to attend this powerbi weekend or not I sincerely wish you all the very best with your upcoming powerbi interview I hope you will get that job thank you so much for watching and check out some of my other videos that show up on the screen or sign up for the powerbi weend bye-bye

Channel: Chandoo

Views: 110,994

Keywords: chandoo, chandoo.org, top power bi interview questions, power bi interview, power bi interview questions, power bi interview questions and answers for experienced, what questions are asked in a power bi interview, pbi interview tips

Id: aZihsRsEYTE

Length: 18min 17sec (1097 seconds)

Published: Tue Oct 31 2023