Let me share a quick tip of Power BI today coz I work with Power BI Desktop a lot recently. 😎
What I am going to share in this post is my favorite shortcut key in Power BI Desktop. Honestly, I know just a few shortcuts in Power BI Desktop (much less when comparing to Excel). Nevertheless, the following shortcut is absolutely my all-time favorite, even a lifesaver! What is it?
Finding and highlighting differences should be a common task in Excel. Most of the time, it’s solved by formula or conditional formatting. Either way, we need to construct a simple formula like the one below to compare the cell contents in two lists.
= A1 = B1
In this post, I am going to show you a non-formula approach which is very handy, especially when we are dealing with a one-off task. What we need is just a few keystrokes, or mouse clicks.
Let’s watch it in action:
Prefer reading to watching? No problem. You may refer to the blogpost HERE or continue to read.
Have you ever encountered something like this? This is quite common. We are sure that the VLOOKUP formula is correct. We are sure that the lookup values are available in the lookup table (101, 106 107 in our example above). What we are not sure is the reason of the error returned… What happened?
The answer is simple: The “101” in B5 is different from the 101 in F5. What?? What’s the difference? The former is text, while the latter is numeric value. This is a basic but important concept for all Excel users, which I had discussed in the post HERE.
To fix the problem, we need to fix the data type in the lookup table. In other words, we need to make sure that all values for Room No. in the lookup table should be text (because the list of the lookup values in column B is text.
You may be wondering; can we simply change the cell format to text. See below:
Unfortunately, it does not work!
We cannot simply convert a number to text by changing the cell format. If we want Excel to accept a numeric input as text, we should set the cell format to Text BEFORE, not after the value is input.
We can use the function ISTEXT to test whether a value is text or not. See below:
Now we understand the problem. It’s time to show various solutions using formula approach. The trick is to use the function TRIM to convert all values under the lookup column into text to make the formula work.
We have an extensive list of numbers in different digits, say from 10 to 13 digits. The problem is, they are supposed to be numbers in thirteen digits stored as text. (Of course, in our example, we work with a short list of twenty numbers only 😉)
Our task is to convert them back to 13 digits as text, not numbers as shown below:
The solution is indeed quite simple. It can be solved with the following formula:
=TEXT(A2,"0000000000000")'copy down where A2 resides the value we want to convert; the 13 digits of "0" is the format string that we want
As simple as this. If 15 digits is required, simply revise the second argument to fifteen zeros (enclosed with a pair of quotation marks) accordingly. Nonetheless, have you wondered why it happened in the first place? This is indeed the main point of this blogpost. 😁
It is a common task to replace a value with another value in Power Query. It can be easily done with “Replace Values”. However if you want to replace a value with the corresponding value in another column, it’s not that straight-forward in Power Query. Most of the time, we would do it by adding a conditional column, followed by removing the original column. It works, but it requires more steps.
Indeed, we could achieve this by using Replace Values in Power Query, followed by a simple modification to the formula generated.
Let’s watch it in action:
Want step-by-step instruction? Please continue to read.
Note: This is not a "how-to" post. In this article, I share my thoughts on how people start their Power BI journey, based on my observation and imagination. 😁
When you say you want to learn Power BI, what do you actually mean?
Power BI is a “relatively” new tool for analytics in business world. With no doubt, Power BI is getting more attention for its great power in visualizing data; in creating stunning dashboard; in getting insights from data by interacting with it, just to name a few. Many people think that Power BI is a visualization tool because this is what they “see” from a user’s perspective. This is super normal. However, what they don’t see, and probably are not aware of, are the building blocks behind the sense.
Hey, don’t get me wrong. These are not the building blocks I talked about.
From my experience, these are the starting points most users user/learn Power BI. It happened because they are users of Power BI report that is created by IT (or someone else). They don’t need to worry about (indeed have no idea) how the data is being pull together from various data sources (where Power Query plays a crucial role); they don’t need to worry anything about Data Model or Power Pivot (how different tables are linked together through relationship); and they have never heard of DAX because they don’t have to. Without any knowledge just mentioned, they are still able to use the report to get information they need.
Regardless of what we do with Excel, Copy and Paste is something we do on daily basis… or even hourly basis. 😁
You may be thinking, every one knows Copy and Paste. Yes, probably. But is every one using Copy and Paste efficiently? In this short video, I will show you 7 Copy and Paste tricks that I wish I knew sooner. Check it out!
Think about this… you have spent hours of work building your wonderful Excel template. Needless to say there are lots of formulas that took you hours of thorough thoughts and considerations, on top of the beautiful formats and layouts. You are very happy with it and you share it to your colleagues. Just a few minutes later, your colleague came to you and said your formula did not work; many errors came out; and seems that someone had “carelessly” messed up some formats and layouts…
You opened the workbook and you found that someone
deleted rows and columns that they are not suppose to…
inserted rows and columns at wrong positions…
applied different color-filled and font sizes in an ugly way…
and many more…
Your mood travelled from heaven to hell in just a few minutes.
Is it familiar to you? If it is, you have to know how to protect yourself cells from editing.
It is super easy to move cells around by drag and drop. The standard drag and drop action however replaces contents in the destination cells. What if we want to preserve destination cells and just shift them up or right? A simple mouse trick could help.
I had blogged about the technique for different scenarios. However I have not yet written a post for the basic of it. So, let’s do it. 😁
What is dependent dropdown?
A GIF tells thousand words:
It is clear, isn’t it?
Then the question is how?
There are many different ways to achieve this because it is Excel. Instead of showing you many different ways, I am going to focus on two approaches. One is for majority of Excel users who are not accessible to Dynamic Arrays (Microsoft 365); and one for Microsoft 365 users.
I didn’t think of blogging about this topic before. I thought that it is so straight-forward. Nevertheless, there is another way of doing so which is even more straight-forward BUT may give you unexpected results later on. And to my surprise, that is the way most people (I encountered) do. Maybe it’s worth blogging about it.😁
It is not uncommon to filter out all values that are not equal to zero. Let’s say we want to filter all records with Unit Sold not equal to zero, it is very common for us to simply uncheck 0 from the list of values. I did that (long before) too. 😅
By unchecking “0” from the list, we thought we have told Excel to keep all values but 0. However, Excel interprets it differently. Let’s see by hovering on the filter icon for unit sold:
We have many different tables to be appended. It should be a simple task with Power Query. You may refer to my blogpost here for the basic of appending tables with Power Query. Nevertheless, life could be challenging in workplace. What if the tables do not have consistent column names? We need to convert all the column names into common names before appending tables. Are we going to read and rename all column names table by table, manually? Of course not! And this is exactly the reason for this post! 😉
Inspirations and the Thinking Process:
Before jumping to the solution, I would like to share with you the inspirations and the thinking process behind the scene.