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.
These are the building blocks of reports/dashboard though
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.
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. 😉
…with and without Dynamic Arrays (which is available to Microsoft 365 users only)
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. 😁
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.
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.
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:
Excel keeps the records that is equal to 100 or 19 or 24 or 31 or 42…… etc.
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. 😉
How can we do it with Power Query without advanced M code? 🤔
Situation:
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.
Don’t make me wrong. I am not complaining Power Query at all. If you have followed me for a while, you should know that I am a big fan of Power Query indeed. It is simply powerful! With Power Query, lots of data cleansing, reshaping, consolidating tasks become easy and efficient. If you want to learn more what Power Query could do, have a look at my blogposts and playlist of videos related to Power Query.
To me, data cleansing is a process with seven stages:
Understanding the expected outcome
Studying the data on hand
Finding the pattern(s)
Cleansing the data (this is the stage most people interested in)
Checking the results
Refining the cleansing steps (most of the cases required, and need to go back to stage 2)
Repeat the above cycles until the expected outcome is obtained
Most of the time, we focus on stage 4, i.e. Cleansing the data itself. Nevertheless, stages 5 to 7 are critical to success, although often got unnoticed.
Let me show you an example here. (Note: this post is not a step-by-step tutorial).
In the previous blogpost, I showed you how to use INDIRECT to prepare a summary table based on values from different ranges across worksheets. In this post, I am going to show you how to do that with Power Query.
Why Power Query? Because it is simply powerful! 😁
With INDIRECT, the summary table won’t update by “Refresh”. When new sheet is added, you have to add the sheet name and extend the formulas manually. With Power Query, a click of refresh would do.
Video with Cantonese VO is under production. Stay tuned. now ready:
Do you think it will be even better if we can do it across workbooks in a folder? Of course we can. Check this out and see if you can applied what you learned here. 😉
There are many #Excel functions that you may not find them useful when you first learn them. Sometimes you may even doubt why there are such functions. INDIRECT should be one of them. 🤔
What INDIRECT does?
It returns the reference specified by a text string.
The syntax is simple:
=INDIRECT(ref_text, [a1])
where ref_text is required. It is the text string describing the cell reference
[a1] is optional. When it is omitted or TRUE, Excel treat ref_text as A1-style reference; when it is FALSE, Excel treats ref_text as R1C1-style reference.
Common examples are in the simplest form.
Say in A1, we input something like B2, B4, D10, etc. (any valid cell reference). And then in another cell, say B12, we reference to A1 using
=INDIRECT(A1)
When we change the value in A1, the formula in B12 returns another value based on the text input in A1. The following screencast shows the effect:
Now I hear you:
Why don’t we simply use a direct reference? We can do the same by inputting the simple direct cell reference in B12
=B2
and we can change the formula to =B4, B6, D10, or whatever valid cell reference, easily whenever needed. What’s the point of using INDIRECT and reference it somewhere else?
I had the same question too… until I found a valid use case of it.
Applying filter in Excel is a very common task. We used to filter a dataset and focus on a subset of data that we are interested in. It is super easy to filter. The problem is, are you sure you have the right dataset being filtered?
Situation
I have a list of files stored in a folder. All files are generated automatically with the same filename convention: “FiscalYearWeekyyyyww.csv”, where yyyy = year; ww = week. The file started from week 01 of fiscal year 2018, all the way to the latest week. The latest file, say, is “FiscalYearWeek202105.csv”.
The task is to combine all files but those for FiscalYear 2018, using Power Query of course. (note this post is not about Power Query, but you may refer to other related post here).
Easy! We can exclude files of fiscal year 2018 by filter rows where ‘Name’ does not contain 2018, as shown in the following screenshot.
If this is what you do, please pause for 10 seconds and think carefully…
Have you heard about all the amazing new functions/features like Dynamic Arrays in Excel 365 but not sure what they are?
Or you have no idea at all how Excel 365 is different from standalone versions of Excel?
If you answer “Yes” to any of these questions, I suggest you take some time to read the blogpost Personal Stock Portfolio Dashboard by MYNDA TREACY , and more importantly watch the video there.
Why?
Because you will see how to build such a beautiful Excel Dashboard in less than 30 minutes using some of the new features available in Excel 365.
Personally, I think this is an excellent video demonstrating some core new features like Excel Data Types and Dynamic arrays. Not to mention how easy an Excel Dashboard can be built in 30 minutes (when you get the skills required). 😁
At the end of the blogpost, you will also see more Excel Dashboard post by MYNDA. You will learn a lot of Excel skills, tricks and tips by reading those posts and watching the videos associated with them.
If you are planning to uplift your skills in Excel Dashboard, consider the popular Excel Dashboard Course by MYNDA TREACY. There is a 20% discountuntil Feb 11, 2021. 👍
So, do yourself a favor and check out the course. The price is incredibly fair, the course is awesome and it’ll transform your Excel reports and possibly even your Excel career. 😉
Disclosure: I make a small commission (at no additional costs to you) for students who join Mynda’s course via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if it doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.
Part 2 – What if we have inconsistent table structures across files?
Note: this is a long post. 😉
This is the continuation of previous post. Suggest you read that post first if you are new to Power Query. Moreover you may download sample data files there to follow along.
In the previous post, we used Power Query to combine all files in a folder. With that built, we can get new data file by a simple click of Refresh. The following screencast shows you that we have combined three data files. When a new file – Week 4 data, comes into the folder, we can easily get the new data by a click of Refresh.
Watch this:
Before Week 4 data, there were 63 rows of data. After we have 84 rows of data.
Isn’t it great? It is awesome! 😎
Then next week, we have data file for Week 5. We feel great as we expect a consolidated table by putting the data file into the same folder, followed by a click of Refresh… BUT…
we didn’t see the date of week 5, but (Blank)? What happened? 😨
Let’s have a closer look at the Queries & Connections Pane
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].
Wanna buy me a drink!?
Click below photo to my PayPal.Me
I appreciate it. :)
