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?
CTRL+SHIFT+L
What it does?
Happy New Year! Wish you all an Excellent Year of 2022!
To kickstart the year, let’s share a trick to solve a common task – highlighting differences in two ranges, with conditional formatting.
Common enough, right?
The ingredients are simple, for most cases, what we need to know are
Are you ready? You may download a sample file to follow along:
Continue readingFinding 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.
Continue reading
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.
You may download a sample file to follow along.
Let’s start with something simple, with a helper column.
Continue readingDo you input a hyphen to represent zero? If you do, please stop doing so in the future. The proper way of displaying “-” for zero is to apply relevant cell format to it.
E.g. Using Accounting format with no currency symbol:
or apply the following custom format string if you want to display number with no decimal and the ordinary +/- sign:
#,##0; -#,##0; - Note: The first, second, and third part indicates how we'd to format positive number, negative number and zero respectively.
It doesn’t matter only if you do not expect further processing / calculations of your data. That should rarely be the case… I guess.
Continue readingThis is an extension of the previous post.
Here’s the task:
If the data is coming from a CSV file, the solution is super easy.
You may download the sample CSV file to follow along.
Continue readingWe 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. 😁
Continue reading
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.
Continue readingNote: 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.
Continue reading
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!
You may download a sample file to follow along.
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
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.
Check it out in this short video!
Prefer a short article instead? Click here.
This is the follow up post from my previous post – How to create a dependent dropdown list in #Excel. As mentioned, it could be easier to illustrate the concept using a video, especially for the use of Dynamic Arrays.
Here comes the videos, in both English and Cantonese. Hope you like it. 😉
English Version:
Cantonese version:
Got a question from a friend recently:
Is it possible to show a different set of questions in a dropdown list depending on the answer input from a previous question using Excel?
Me:
Of course! It is just a dependent dropdown list using Data Validation.
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. 😁
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.
You may download a sample file to follow alone.
Continue reading
Did you experience a slow workbook? How slow? Let’s say it takes more than 2 minutes to just open the file.
This is actually what a colleague asked me for help. When I opened the workbook, I saw LOTS of SUMIFS across tens of worksheets. And all SUMIFS refer to a common dataset on the workbook.
Then I asked: “Would you consider using Pivot Table instead of so many SUMIFS?”
The answer was “NO” as she needed to use formula to return results that fit into the “pre-set” table layouts, which I totally understood… such a common workplace’s scenario!
Then I proposed GETPIVOTDATA instead of SUMIF(S).
Her responses told me that she didn’t know about GETPIVOTDATA. Maybe I should write a blogpost about GETPIVOTDATA. Before that, let’s test if GETPIVOTDATA is more efficient than SUMIF.
Continue reading
Don’t get me wrong. I am not talking about the Filter button sitting on the Data Tab of ribbon,
although they look almost exactly the same.
I am pretty sure that most people who use Excel five days a week is not aware of “AutoFilter”.
You may help strengthen/correct my understanding by a quick poll: 😁
Well, maybe you know about AutoFilter… but are you sure you know the “hidden” features of it?
There are two features, a standard one and a special one, of AutoFilter that I find really helpful. Let’s check it out.
You may download a sample file to follow along:
Continue readingI 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.😁
You may download a sample file to follow along.
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:
What??? 🤔🤔
Continue reading
Did you notice that there is a “Reapply” button next to Filter on the Data Tab of ribbon? And wondering what it does? 🤔
Indeed, the “Reapply” button is dim until you have applied filter to at least one column.
To illustrate how to “Reapply” filter, let’s start with a simple example.
Continue reading
In the previous post Rename column names in a dynamic way with #Excel #PowerQuery, I talked about the key steps involved to rename column names in a dynamic approach; and the inspiration as well as the thinking process.
As mentioned at the end of that post, we could apply the techniques for a practical and common problem – Append tables with inconsistent column names, as shown in the diagram above.
As promised, here’s the video to illustrate how to do it. I hope you like it. 😉
Continue reading
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! 😉
Before jumping to the solution, I would like to share with you the inspirations and the thinking process behind the scene.
Continue reading
Disclosure: The author earns a small amount of commission (at no additional cost to you) for the product/service successfully sold through the links of [Affiliate].
New Generation Finance - Accounting - Controlling using Microsoft BI stack
A Power BI Creator Blog
Exploring open roads without breaking the bank
Work smarter by Mastering Functions in Excel
Your next stop in mastering Power Query and Power BI
Work smarter by Mastering Functions in Excel
Leading Consultants on Power BI and the Microsoft Data Platform
Helping lawyers make the most of Microsoft Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
More geeky stuff from the author of www.excelguru.ca...
Work smarter by Mastering Functions in Excel
Peltier Technical Services - Excel Charts and Programming
Turn your data into opportunity
Q&A about Excel
wmfexcel 