Power BI Interview questions: a different way to know your applicants

Power BI Interview questions: a different way to know your applicants

One day you will have to recruit or will be involved in a recruitment process for a Power BI position. Nobody knows everything about a tool or a subject, that's also why our job is fun, we never stop to learn.
Asking very precise questions aren't always the best solution, I think it's also nice to ask "open" questions. It's a way to learn from your applicant and to understand how they will deal with their daily job.
I tried to write a list of questions I would like to ask (and maybe, yes, to answer). 

Questions list

  • What are the different languages used in Power BI? Can you explain their usage?
  • Do you know one data modeling concept? If yes, can you explain it in your words?
  • We have to transform our data, where and how can we do it?
  • What’s the difference between these two visuals: Table and Matrix
  • By dragging and dropping a field in the Values area we can create implicit measures. Could you give me a shortlist of different aggregation functions available?
  • We want to implement security in our data. Do you know a way to hide some data to a group of users?
  • When we select a table in Power BI desktop, we have an option called “Mark as date table”. Do you know why?
  • Users can filter data in multiple ways! Can you give me some examples?
  • Do you know what Power BI Datasets are, and why would you use them?
  • Power BI community gives us a lot of different External tools, do you know some of them?
  • I’m the IT manager and I never heard about Power BI before. Can you explain to me what it does and what’s the gain to use it?
  • If you know DAX, can you give me 3 functions that you use the most?
  • We are wondering if we need to buy Power BI Premium, do you know which functionalities are available in Premium?
  • When you need to build a report, where do you start your journey?
  • Which points are the most important to not fail a Power BI project?
  • You start a new project, your first mission is to reduce the size of the dataset. Which are the different operations you will perform?

It exists already a ton of different lists of questions available on the internet, I wanted to break the rule with a different kind of question.

If you want to add more questions to the list, feel free to send me a message.

Your data model first!

Your data model first!

At this end of the year, I thought it’s time for me to bring you a gift!!
It’s a very important subject that I mention to my coworkers and it’s 80% of a success power bi story!

All the focus is on the tech part.

All developers (yes, you are!!), spend a huge amount of time to understand their sources, their transformation, and the magic that happens in their DAX measures!
And, to be honest, that’s normal!

So why this article?

After 1 week, 2 months, or more time, we are very proud of our dataset! (Yes, I’m not talking about reports, but dataset which can be used in many reports)
Our model contains a lot of data, all the information needed is there! But is it easy for an external person to use it? (a User, a colleague, your kids, ..)
Sometimes yes, sometimes no.

Some complexity exists:

  • Too many tables
  • Too many relationships and the difficulty to know the interaction between tables.
  • The model keeps the complexity of the source systems.

But ok! We did a huge work to clean and provide this model! Everything work!! Our users can take 2 or 3 hours and try to understand the logic behind it!

I like to mention it. I love cars!! But I know nothing about it!! Do I need to understand how an engine work to drive a car? It’s a real plus, but not mandatory.

So what do I suggest?

It doesn’t take much time, and it can help you to keep an eye on your goal. (Yes!! Provide a very shiny dataset)
Before every model, I recommend you to draw your model on a sheet of paper.
You already know the information you have to present and how your user group this information. If not, it’s the perfect time to ask your users and write their buzzwords on paper.

Before each project, ask them to present you their job and the different tools they use. It would help you to have a clear view of their logic behind, their needs, and also what’s missing now.
At this point, I only write huge keywords on paper.

When I’m back on my desk, I’m looking at this brainstorming paper, and try to group the different keywords into a logic.
That’s how tables bring to life, and because I’m a Kimball huge fan let’s call them Dimensions!

So now, with these tables you have the user point of view of their data, all the magic (and fun) is to prepare the data to fit in it!
But hey, with Power Query, SQL, it’s just a piece of cake! Isn’t?

In conclusion

Do not jump directly into the source’s data, keep an eye on your expectation first. Ask your users, talk to your colleagues and try to build the model of your dream.
It’s possible! During your development, you may change some tables, and that’s completely normal! But at least, if you keep your original model, everybody will win in the end.

I wish you all the best in your data journey!
What a fun job!!

Power BI Create a Calendar table automatically

Power BI Create a Calendar table automatically

In our models, the best way to deals with dates and DAX intelligence functions is to use a Calendar Table (Dimension). Good news, this table can be created in 2 clicks with Power Query (Transform Data) in Power BI!

Go transform data and right click on Queries to create a new Blank Query

Copy / Paste the following code

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
  in
    InsertWeekEnding
in
  CreateDateTable

Choose a start date and an EndDate, click Invoke
That’s it! You have a beautiful calendar table.
Do not forget to mark this table as a data table

Credit: I found this code on internet many years ago and used it in the LazyDAX file. I think I changed it, but I can’t redirect and credit the one who created it. I think it can help you a lot, that’s why I’m sharing it with you 🙂

LazyDAX v3

LazyDAX v3

I created this Power BI desktop file (.PBIX) and share it with you.

 

 

 

Simple and clear data model

 

 

 

small data presentation

 

 

 

Quick explanation and examples

LazyDAX doesn’t include long and detailed explanation or comments. For each measures and function you have a link to microsoft.com, sqlbi.com or other external website.

I would like to teach you DAX from A to Z, but it’s not the best way to do it. (At least because I’m not one of the 2 italians! I would love to have their accent!! But I’m not!!). At least for me, I learned by doing mistake and try again and again.

IMO, to learn DAX, we need to practice! And that’s what I provide you, a place where you can add / modify your DAX measures.

You can still follow links and read the Microsoft documentation! That’s the reason why I changed my presentations to keep the same Canvas as Microsoft functions lists.

 

 

 

Microsoft Documentation

 

 

 

LazyDAX function list

Why?

Now, I use DAX everyday, but my journey was long and complex! How is it possible? Before my lovely job in BI, I was a “pure” developer. (C#, Java, PHP, SQL, VB, VBA, Perl, Pascal, C, Cobol, Fortran, Ruby, … I worked and learned a ton of different languages)

BUT DAX took me a lot of time to understand! I still learn new functions or logics. I hated and loved it a lot!

Now it’s the big love and I’m trying by many methods to explain it to different people. (It’s another level of complexity)

With this updated version of LazyDAX, I wanted to provide a simple and more clear file!

This is the Power BI file where you are the hero!! DAX is your unique weapon!!

I plan to add more functions and explanation in the coming months!

How to stay always “Available” in Microsoft Teams with Excel

How to stay always “Available” in Microsoft Teams with Excel

Or create your own Excel file by reading the text below (and copy / paste the vba code)

Why?

First it was a private joke, with this article it’s now a public joke.

Our client switched from Skype to Teams and it was a very cool improvement. My colleagues told me with a bit of fun: “Teams is cool, but you can’t change the time before appearing away!”. And it’s true, we can’t! Somebody said, “If you install a software who will click randomly on your screen, you will not appear away anymore”. Yes,.. but our software policy does not allow us to install anything on our computers.

It gave me an idea! Thanks to our very good old friend, Excel! People will say “Lazy is smart”, Arnaud is “funny” or any other reason. But I had fun with Excel and his perfect VBA editor.

My colleague Isabelle (and also Maslow) will say :

https://en.wiktionary.org/wiki/if_all_you_have_is_a_hammer,_everything_looks_like_a_nail

My hammer is Excel and Code.. I agree.. (nail or snail.. it’s just a “s” who is missing)

I created a small excel sheet who will loop until you ask it to finish and will move your mouse cursor every 5 seconds.

My joy is shared between.

“I’m the Microsoft BI guy who uses Microsoft Office and VBA code to do a joke”.

and

“People will see me as a very lazy guy!! isn’t the name of his website!?!”.

Anyway, if you read this article, then it’s because I think you will pick the first idea. I’m I wrong? 🙂

Where can you download it?

You can download the excel sheet on my github repo.

(If you want your own excel sheet, the code is also available at the end of this post)

Thanks to Rafi for his 64 bits version!

How does it look like?

This is how it looks in Excel. You can start the timer by clicking on the start button or CTRL + l (like lazysnail) or start manually the macro named “DoNotSleep”.

To stop the timer, you have to press any key.

Do you have the VBA code?

If you are curious and want to build your own Excel, let’s copy the following VBA code:

Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As LongPtr, ByVal y As LongPtr) As LongPtr
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As LongPtr, ByVal dx As LongPtr, ByVal dy As LongPtr, ByVal cButtons As LongPtr, ByVal dwExtraInfo As LongPtr)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As LongPtr = &H8
Public Const MOUSEEVENTF_RIGHTUP As LongPtr = &H10

Sub DoNotSleepPlease()

Dim i As Integer

For i = 1 To 9999
    'For Info, number of iteration
    'Cells(1, 1) = i
    If Cells(3, 5) = "" Then
        SetCursorPos 200, 200 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
        
        
        SetCursorPos 300, 300 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
        
        SetCursorPos 400, 400 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
        
        SetCursorPos 500, 500 'x and y position
        mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
        mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
        WaitPlease
    Else
        Exit For
    End If
    Next i
End Sub


Sub WaitPlease()
    Dim sngWaitEnd As Single
    sngWaitEnd = Timer + 5
    Do
      DoEvents
      Cells(3, 3).Value = Timer
    Loop Until Timer >= sngWaitEnd
End Sub