Some tips. Wish I had known them before.

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…

A concept made simple (with trains).

Photo by Markus Spiske on Unsplash

I am what you term a “super commuter”. I used to wake up at 5 am. Leave the door at 5:30 am, catch the train and arrive at work by 8:00 am. It sounds like a nightmare. I love my job and I love it enough to do that. Then the pandemic came and it’s not something I have to do as often anymore. I am thankful of that, but what does this have to do with Power BI filter and row context?

You see, filter and row context can be seen in common situations if you know what you…

Do this with Power BI, R, Power Shell or CMD

Photo by Denise Johnson on Unsplash

Have you ever had to combine multiple CSV files? I’m sure it’s a task every data analyst or data scientist must do at some point, and a strange task really. Why are they separated in the first place? Isn’t it better to connect to the source? Sometimes the best solution may not always be possible.

If you find yourself opening Excel, copying files and juggling 10 different workbooks, then this article is for you. I’ll show you different ways of appending files. Not only will you save time, but it will save you from errors as well. …

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

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 should you use it? How do you use it?

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…

A visual explanation of what is happening.

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.

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…

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

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.

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…

…I am putting several articles into a content generator.

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…

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