Comprehensive video showing very useful ways of using VLOOKUP. Explains when to use Range Lookup with easy examples.
Learn why in this short, 5-minute video.
More articles on VLOOKUP are here
We have already learnt how to import and clean data from various sources using Power Query (Transform). Once the data is inside Power BI Data tab (or Excel Data Model of Power Pivot), we can refine and enrich it further using various features like synonyms, custom sorting order, formatting, summarization and more. Watch the video for details – English as well as हिंदी versions are available.
Using Power Query, this type of work is extremely painless and quick. What’s more it refreshes data in just a click when more files are added.
This is a part of data clean up series based upon the 10 rules for clean data. This video covers rules 1, 5, 6, 10 – cross-tabs, data growing horizontally and multi-layered headings.
We spend lot of time in data cleaning. But there is no simple definition of exactly what clean data means. Therefore, I created a simple 10 item checklist. If all 10 items are correct, data is clean. Simple and easy to use.
10 rules for Clean data
Remember, here we are talking about input data – not output (reports).
- Each column must have a heading
- No blank headings
- No duplicate headings
- No formulas in headings
- No merged cells
- Each column must have ONE meaning
- Each column must have ONE type of data
- No subtotals or grand totals
- Formatting should not be used instead of data
- Data must grow vertically, not horizontally
Convert clean data to a Table
If you are using Excel, as soon as data is cleaned, convert it into a Table. Why? Here are the benefits of using Excel Tables – 13 articles.
New and very useful feature for artists and designers. Specify accurate colour value using HEX code instead of RGB or HSL. What difference? Well, HEX codes are most commonly used. RGB or HSL require you to put three values. HEX is ONE value. Works with all Office apps: Word, Excel, PowerPoint, Outlook, OneNote, Access, Project, Publisher, and Visio.
By the way, this colour shown here – #0F4C81 is special. It is called Classic Blue and it is the Pantone colour of the year 2020.
If you post a long video to YouTube, it is a good idea to add a Table of Contents to the description. This helps viewers go to specific topics quickly. In my case, I post the video link + TOC on my blog as well. The link format for YouTube and regular HTML are totally different. That is why I made an Excel based YouTube TOC creator.
The old comments are now called “Notes”. The New Comments are actually discussion threads – like in Word. You can type a comments and others can reply to it. Spend 4 minutes to learn about the new comments.
New Comments look like this… a pink sign in the top right corner.