Some tips. Wish I had known them before.

Image for post
Image for post
Photo by tian kuan on Unsplash

When you think of modelling, what do you think of? A lot of intricate joins, look ups, or maybe just frustrations?

Have you experienced this before? You have a measure, but it doesn’t give you the correct answer. You wonder why. You are not exactly sure why, so you tweak the measure. Perhaps it’s something in the table, maybe a data issue, so you tweak it in Power Query as well. Then you end up with nothing…

Using table functions to monitor SharePoint files and setting alerts using Power Automate

Image for post
Image for post
Photo by Murai .hr on Unsplash

Do you use SharePoint? Personally, have not liked it all that much. I have had to use it a lot in the past year because of the pandemic. I have fallen in love with it to say the least.

SharePoint demonstrated its usefulness when I had to ask various units to fill in workbooks on SharePoint. There was a total of 50 workbooks. It would have been so much easier if I were able to put all the workbooks into one document but due to the nature of the work, they had to be separated.

Here is the problem — when users interact with the workbooks, extra columns, blank rows, and data issues begin to appear. (Some folks just like to add data when it is not asked for). I could not put full restrictions on the workbooks itself because it would disable sorting and filtering. I had to find a way to monitor the workbooks so if there are errors, I can be notified right away. …

When should you use it? How do you use it?

Image for post
Image for post
Photo by David Clode on Unsplash

Admittedly, when it comes to data modeling, I’ve always tried to keep the model to one fact table. The reason is simple, it’s easy to organize and it’s easy to read. If you know your dimension tables and your fact table, you can already answer the question of whether you can aggregate by dimensions or “slice and dice” by categories.

The problem occurs when you need to add another fact table to a model. It’s tempting to try to add everything into one big fact table or to “flatten” the fact tables, but this is not always possible.

I remember trying over and over to fit one single fact table in a model — I’ve tried many methods, from using R scripts to using various DAX functions to creating virtual tables, but sometimes this is not always the best solution. Sometimes the best solution is to use a multi-fact table model. …

A visual explanation of what is happening.

Image for post
Image for post
Photo by Samuel Sianipar on Unsplash

If you are new to Power BI, you will eventually see a function call CALCULATE. There are many explanations as to what it is and what it does, and some can be quite complicated. CALCULATE is a very important function and understanding it will give you a whole new appreciation of Power BI.

A co-worker once said to me that he is not impressed with Power BI, he thinks of it as MS Access, and the visualizations are just sugar coded crap. This is before he learned what CALCULATE can do. …

…here is what it means.

Image for post
Image for post
Photo by Kevin Ku on Unsplash

I remember when I first started working, I didn’t know anything about Excel and didn’t know much about work culture. I graduated fresh from school and I was lucky enough to be hired as a summer student in a data entry role.

I always told to be “Detail-Oriented” or better yet, work on the details. I honestly didn’t know what the hell that meant other than to double-check my work.

What does being detail-oriented mean? To some, being detail-oriented means you have to be completely organized at all times. To others, it is having impeccable grammar. …

…slice top n by groups. A useful summary to have.

Image for post
Image for post
Photo by Greyson Joralemon on Unsplash

Top N by groups. I personally think it’s a very underrated way of exploring data. It’s like a saw, you just want to saw off the first few rows of a data set for analysis. I’m sure you have had to group summaries before, but you can also do some cool EDA with it using Power BI.

If you are able to group the data you have, get the difference of the top 2 items by the group, you almost always find something interesting there. …

…usefully tools for your data journey.

Image for post
Image for post
Photo by Matthias Heil on Unsplash

This article will introduce you to the Contains functions with lists. It’s a great function to use in Power Query. There are little nuances for each but I hope this article will help you get started.

Lists in Power Query are written with {} brackets. They can be {1,2,3} or even {1, “a”,123}. If you like to experiment, you can go to Power Query >Create a Blank Query > Advanced Editor > Replace the source information with > Source = {1,”hello”,123}.

It’s important to remember this — columns in Power query can be wrapped in {[Column A]} to return a list so you can use list functions. …

…I am putting several articles into a content generator.

Image for post
Image for post
Photo by Michael Dziedzic on Unsplash

I’ve been writing on Medium for 3 months now. It has been pretty good. Writing is a good side hustle, you get to write where you want, when you want and whatever you what.

I love sharing and gaining knowledge, if you have time, check out my articles :)

Sorry for the plug — but I am all about efficiency and I’ve always wondered if there is a way to use AI to generate content for me. Writers out there will probably scoff at this, but I am still very curious. I’ve always wondered.

Well, perhaps it’s time to give it a try! …

…Could have used these clarifications when I first started.

Image for post
Image for post
Photo by Robert Anasch on Unsplash

Why is there a calculated column and why are there measures? Which one should I use? Which one is faster? When do I use it?

These are all good questions. I have asked myself the same when I first started. As I worked more with Power BI, I realized that they are quite different and work differently. It’s helpful to know these differences sooner rather than later.

How? To start, they are loaded differently in Power BI, they impact your visuals differently and also one works with row context while the other filter context.

But before we get too deep into it, let’s take a look at what exactly is each and we will walk through an example as well…

…using Table.FindText to search for anything and anywhere in your file.

Image for post
Image for post
Photo by Rabie Madaci on Unsplash

I’ve always been amazed at the simple find function. You can find anything that matches your criteria, even if your search item is hidden in different work sheets, open text or tables.

Just imagine, you can search for anything that appears anywhere in your raw data. Even if it is hidden somewhere in an open text field. Quite amazing!

How is this helpful? It’s very helpful if you are looking for a key word or a set of numbers that has importance, but you just don’t know where to look. …


Peter Hui

I love working with data. HR and Psychology background. Microsoft certified data analyst. Power BI and R are my favorite tools. Cleaning and wrangling? YES.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store