Power BI Tip #5: All About Slicer Controls in Power BI

Learn Slicer Control in Power BI at Intellisoft Singapore

Slicers are really the key feature in Power BI, that make the Dashboards & Visualizations come alive. They add a bit of interactivity in the dashboards and allow the users to select, slice and dice the information in different ways and methods.

With a slicer, you can choose any available year, and the entire report can be filtered to that particular year, or month, or product category, fiscal period, department or division.

Slicers can be created using Checkboxes, Radio buttons, Flat Chicklet style buttons and can be formatted to allow a single, multiple or all value pick.

Since there are a plethora of features, I decided to record a short Power BI tutorial highlighting the key features of Slicers in Power BI.

Hope you will benefit from these features and will be able to build some interactivity in your Power BI dashboards.

Hope you found this tip useful.

For more tips on Power BI, head over to our YouTube Channel, and subscribe to it, to be notified of latest videos and tutorials.

Recommended Reading:

For tips on loading data from the result set of another query, you can use the Reference feature of Power Query

Power BI shows only rows where data is found. If you want to see all rows, you’ve got to see this tip on Showing Rows With No Matching Rows.

Learn Power BI From Practicing Professionals in Singapore

Intellisoft Systems conducts PowerBI training in Singapore each month. Do attend our hands-on practical training to learn Power BI from the beginning, and be able to analyze and visualize data easily with Microsoft tools.

Visit PowerBI Training in Singapore or email to training@intellisoft.com.sg for a course brochure.

Written & Presented by: Vinai Prakash,
Founder & Master Trainer, Intellisoft Systems

Vinai conducts the Microsoft Power BI training in Singapore. His Power BI courses are extremely popular, fun and easy to learn for beginners and experienced professionals alike.

Join Vinai in his next Power BI training course at Intellisoft. You won’t regret it!

How To Clean Data Using Power Query

Power Query To Clean Data in Power BI and Excel

Data cleaning is one of the most critical steps in any data analysis process. Without clean, structured, and reliable data, insights drawn from analysis can be inaccurate or misleading.

In Power BI, Power Query serves as a powerful tool that allows users to connect, transform, and clean data efficiently, ensuring that it’s ready for reporting and analysis.

Whether you’re working with messy datasets from multiple sources, dealing with missing values, or eliminating duplicates, Power Query provides a simple, yet robust interface to clean data with minimal coding.Power Query To Clean Data in Power BI and Excel

In this guide, we’ll walk through a 10-step process to clean data using Power Query, providing clear and actionable instructions to ensure your data is refined and ready for analysis.

10-Step Process to Clean Data Using Power Query in Power BI

  1. Load Data into Power Query
  2. Remove Unnecessary Columns
  3. Rename Columns
  4. Filter Out Unwanted Rows
  5. Handle Missing Values
  6. Change Data Types
  7. Remove Duplicates
  8. Trim and Clean Data
  9. Split and Merge Columns
  10. Apply and Load Data to Power BI

Step-by-Step Process & Details on How to Use Power Query in Excel / Power BI

1. Load Data into Power Query

The first step is importing your data into Power Query. This could be from an Excel file, SQL database, or other data sources.

  • How to do it: In Power BI, click on Home > Get Data. Choose your data source and load the data into Power BI. Then click Transform Data to open Power Query Editor.
  • Purpose: This step allows you to connect Power BI to your data source, bringing raw data into the environment for cleaning and transformation.

2. Remove Unnecessary Columns

Not all columns in your dataset are needed for analysis. Removing irrelevant columns helps streamline the dataset and improve performance.

  • How to do it: Select the columns you don’t need, right-click, and choose Remove Columns.
  • Purpose: This reduces the size of your dataset, making it easier to work with and removing noise that could affect analysis.

3. Rename Columns

Renaming columns improves readability and makes your dataset more understandable, especially when working with multiple datasets or sharing reports with others.

  • How to do it: Right-click the column header and choose Rename. Alternatively, double-click the column name to rename it.
  • Purpose: Clean, descriptive column names make it easier to recognize and use data fields in future transformations and analysis.

4. Filter Out Unwanted Rows

Filtering data ensures that only the relevant rows are kept for analysis. This is particularly useful when you have data entries like errors or outliers that can skew your results.

  • How to do it: Click the dropdown arrow in the column header and apply filters based on conditions (e.g., removing rows with zero values, errors, or irrelevant categories).
  • Purpose: Filtering reduces dataset size and removes irrelevant data, focusing on what’s important for your analysis.

5. Handle Missing Values

Data often has missing values, which can create issues in analysis. You can either remove rows with missing data or fill in values where appropriate.Use Power Query to Clean Data in Power BI. Join Hands on Training at Intellisoft Singapore

  • How to do it: Right-click the column and select Replace Values to fill missing data, or use Remove Rows > Remove Blank Rows to eliminate incomplete records.
  • Purpose: This ensures your dataset is complete or that missing data is handled in a way that doesn’t negatively impact your analysis.

6. Change Data Types

Correctly assigning data types (e.g., text, number, date) is crucial to ensure that Power BI interprets your data correctly.

  • How to do it: Select the column, then go to the ribbon, click on the Data Type dropdown, and choose the appropriate type (e.g., Decimal Number, Date, Text).
  • Purpose: This avoids issues like date misinterpretation or incorrect calculations due to mismatched data types, ensuring smooth analysis.

7. Remove Duplicates

Duplicated data entries can skew your analysis by inflating totals or introducing inaccuracies. It’s important to identify and remove any duplicates.

  • How to do it: Right-click the column where duplicates might exist, then select Remove Duplicates.
  • Purpose: Removing duplicates ensures that each data entry is unique, resulting in accurate and reliable reports.

8. Trim and Clean Data

Text data often comes with leading or trailing spaces or non-printable characters. Cleaning this data ensures consistency.

  • How to do it: Use Transform > Format > Trim to remove unnecessary spaces, and Clean to remove non-printable characters.
  • Purpose: Trimming and cleaning text data ensures consistency and prevents potential errors when joining datasets or conducting analyses based on string matching.

9. Split and Merge Columns

Sometimes, data is combined into one column and needs to be split (e.g., first and last names, date and time). Conversely, you may want to merge multiple columns into one (e.g., creating a full address from separate fields).

  • How to do it:
    • For splitting: Select the column, go to Transform > Split Column by delimiter (e.g., space, comma).
    • For merging: Select multiple columns, right-click, and choose Merge Columns.
  • Purpose: Splitting and merging columns helps you organize your dataset in a way that aligns with your analytical goals.

10. Apply and Load Data to Power BI

After completing the data cleaning, the final step is to apply your transformations and load the data back into Power BI.

  • How to do it: Click Home > Close & Load. This will apply all transformations and load the clean data into Power BI for analysis.
  • Purpose: This finalizes the cleaning process and makes your data ready for visualization, reporting, or further analysis in Power BI.

Conclusion

Cleaning data with Power Query is a vital part of any data analysis process in Power BI. These 10 steps will help ensure that your data is clean, reliable, and ready for actionable insights. By following this structured approach, you’ll minimize errors, streamline analysis, and set the foundation for building accurate and meaningful reports.

 

Top 10 DAX Expressions in Power BI

Top 10 Functions in Power BI DAX

Top 10 DAX Expressions in Power BI

As a Power BI trainer at Intellisoft Systems, and writer of several Excel & Power BI articles, I’ve worked with with over 15,000 business professionals, data analysts, and executives over the years.

When people first approach Power BI, they often marvel at how it brings their data to life with interactive dashboards and visualizations.

But as they dive deeper, they soon realize that Power BI’s true power lies not just in its visuals, but in its ability to manipulate, analyze, and derive insights from data using DAX (Data Analysis Expressions).

Most Important DAX Functions in Power BI
Most Important DAX Functions in Power BI

My Journey with DAX in Power BI

I remember my own journey into the world of DAX, which felt a lot like learning a new programming language. At first, the syntax was daunting, and it seemed like every time I thought I understood how a function worked, a new scenario would throw me off.

However, as I started teaching Power BI Courses around the world in our public classes at Intellisoft, and as I assisted hundreds of data analysts in creating their Business Intelligence Dashboards in Power BI, I noticed a pattern.

The same set of DAX expressions came up repeatedly as crucial for solving business problems, answering analytical questions, or building complex reports.

The beauty of DAX is that, once you grasp the core concepts, it opens up endless possibilities for data transformation and modeling. It’s not just about creating formulas—DAX helps you enhance your reports by extracting hidden patterns and making data-driven decisions easier.

With Power Query to Clean the Data, and DAX to enhance the analysis of data, it is the best of both worlds.

Why Create DAX Expressions?

In the world of data analytics, basic reports often don’t tell the full story. As professionals, we are expected to go beyond the surface-level numbers and reveal patterns & insights that drive business strategy.
DAX Functions in Power BI
Here’s why DAX expressions play such an essential role in this:

  1. Advanced Calculations: DAX allows you to perform calculations that Excel or standard report-building tools can’t easily handle. Whether it’s aggregating data across different periods, creating year-over-year growth metrics, or applying conditional logic, DAX gives you that edge.
  2. Data Relationships: Power BI thrives on relationships between datasets. DAX lets you define those relationships more clearly, ensuring that your reports reflect the real connections within your data.
  3. Custom Metrics: Every business has unique key performance indicators (KPIs) and metrics. DAX allows you to create custom measures that align with your organization’s needs, ensuring your reports are tailored to the business context.
  4. Dynamic Reporting: DAX enables dynamic filtering and context-driven calculations. This allows you to create reports that respond in real-time to user inputs, making your dashboards far more interactive and meaningful.
  5. Enhanced Performance: While Power BI’s drag-and-drop interface makes it easy to start, large datasets or complex models can slow things down. Writing efficient DAX expressions can optimize your data models, improve performance, and ensure that your reports scale with your data.

Top 10 Most Useful DAX Expressions in Power BI

Over the years, I’ve seen which DAX expressions are most commonly used and how they help professionals derive actionable insights. Here are the top 10 DAX expressions that every serious data analyst, executive, or manager should know:

  1. CALCULATE()
    The most versatile DAX function. It lets you modify the context in which a calculation is performed. Use it to create measures that behave differently based on filters.Example:
    CALCULATE([Total Sales], 'Calendar'[Year] = 2023)
  2. SUMX()
    A powerful iteration function. SUMX allows you to perform row-by-row calculations across tables. It’s essential for scenarios where you need to evaluate expressions in each row before summing the result.Example:
    SUMX(Sales, Sales[Quantity] * Sales[Price])
  3. FILTER()
    Used to return a table of data based on a filter expression. It’s particularly useful when working with complex conditions that you can’t handle with basic filtering.Example:
    FILTER(Sales, Sales[Region] = "East" && Sales[Amount] > 10000)
  4. RELATED()
    This function retrieves related data from another table. It’s handy when you have a relationship between tables and need to pull in values from related tables.

    Example:

    RELATED(Products[Category])
  5. DIVIDE()
    A safer way to perform division, it handles cases where division by zero would otherwise cause an error. It’s more reliable than the simple “/” operator.Example:
    DIVIDE([Total Sales], [Total Units], 0)
  6. ALL()
    Removes any filters that may be applied to a column or table. This is useful when you need a calculation that ignores certain filters, such as when calculating overall totals.Example:
    CALCULATE([Total Sales], ALL(Sales[Region]))
  7. RANKX()
    Allows you to rank items based on a specified expression. It’s invaluable for comparing performance, such as ranking sales by regions or products.Example:
    RANKX(ALL(Sales[Region]), [Total Sales])
  8. EARLIER()
    Often used in calculated columns, EARLIER enables row-by-row context in calculations where you need to refer to previous row contexts.Example:
    CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Product] = EARLIER(Sales[Product])))
  9. SWITCH()
    Functions like a multi-conditional IF statement, allowing you to return different values based on specific criteria.Example:
    SWITCH([Rating], 1, "Poor", 2, "Average", 3, "Good", "No Rating")
  10. DATESYTD()
    A time intelligence function that calculates the year-to-date total of a measure. It’s essential for tracking cumulative performance over time.Example:
    DATESYTD('Calendar'[Date])

Microsoft is adding new DAX functions almost every month. There are already over 800+ DAX functions, whereas in Excel there are only about 400+ functions. Whatever calculation you are planning to build, there is most likely a DAX function that can be used to simplify the expression. It is worth learning new DAX functions and reviewing the entire function list to checkout the functions you can use immediately.

Conclusion

Mastering DAX is a continuous learning journey. It can seem overwhelming at first, but once you understand its logic and structure, you’ll realize how powerful it is in creating actionable insights and making sense of complex data.

In my Power BI classes, I always encourage participants to experiment with these expressions, building them into their regular reporting process. Once you start using DAX effectively, you’ll wonder how you ever managed without it.

Creating DAX expressions in Power BI allows you to push your data analysis skills to new heights. Whether you’re an analyst diving deep into data trends, or a manager looking for quick, actionable insights, these top DAX functions will empower you to extract the maximum value from your data models.

Power BI MasterClass in Singapore at Intellisoft Systems

How To Write An Effective Email

Learn to Write Effective Emails at Intellisoft Singapore

Most business communication happens via email with virtual offices, global clients, and multiple time zones. Yet, most people struggle to write great, effective and professional emails.

The result:  People judge you by your emails. If the email seems unprofessional or buggy with mistakes, your and the company’s reputation is immediately tarnished.

Fortunately, writing compelling and professional emails within a few minutes is extremely easy. Just remember to take note of the following tips.

1.  Subject lines: Always use a simple, to-the-point subject line. Ideally, it should be no longer than 5-7 words. Most people look at the subject line to decide if they are going to open, and read the email, or simply delete it, irrespective of the content.

2. Greet: Always provide a simple greeting at the beginning of the email. It could simply be Hi Susan, or Hello Richard.

3. Thank for something: If this is a follow-up email on something, or you are replying to someone’s email, it is a good idea to thank the person for replying to you. Thanks a lot for getting back to me so quickly. Thanks for your time on the phone, or Thanks for the meeting. By thanking the person, you make it a more pleasant email, and the recipient is put at ease with your nice comments.

4. Reason / Objective of Email: The key reason for writing the email should be very clear and concise. There is no need to write long-winded emails, as no one has time to read that much. People nowadays scan emails to quickly find the most important thing, and then decide what to do with it. You should quickly come to the point, like

We need to meet for a short while to discuss and resolve xyz, or
I need your help to review xyz, or I am writing with regards to xyz.

5. What you Want them to do / Ask: People scan emails to find out if they have to do anything about it – keep it, file it for the future, or take any action.  If you want them to do something for you, you must state it clearly. If this is not clear, then they won’t do anything. Keep it short, simple and easily scanable. For example,

  • I want you to arrange a meeting between the 3 parties – possibly by the 15th of the month.
  • I would appreciate it if you could review the attached proposal and get back to me by the 15th Nov.
  • Could you please reply which time suits you better – Monday at 11am or Tuesday at 4pm? 

By putting an action, and an action by date, you clarify what the recipient has to do, and by when. There is no room for ambiguity, and you are more likely to get what you wanted, by the due date.

6. Add Closing Remarks: It is a good idea for you to thank the recipient one more time, and add some nice, polite closing remarks, like:

  • Thanks for your help and support.
  • Thanks for your cooperation and support.
  • Feel free to contact me if you have any questions or concerns.  I look forward to hearing from you.

7. Signature: It is a must to have a simple yet clear signature. You should not end with Cheers or See Ya unless it is a friend you are writing to. For business writing, you must always say, With Best Regards, or Sincerely, or Thank You. Regards, Richard.

8. Spell Check & Grammar Check: It is essential to always do a spelling check and a grammar check. Nothing spoils a good email than a few typos. All good email packages have a built in spell check. You should make use of it, and even set your email configuration to always do a spell check before sending. This can make your emails more professional and set up you in the eyes of the recipient.

Common Pitfalls in Writing Professional Emails & How To Avoid Them:

  • Writing in All Capital Letters: Writing in all caps is considered offensive, rude on the internet. You should write Subjects in Title Case to stand out. The email body should be using normal English language rules – first letter is in capital, and the rest in small case. Use appropriate punctuation, and avoid using too many exclamation marks or question marks etc. It looks quite amateurish.
  • Using Vague Subject lines: Do not try to fool the recipient to click your email by sending suspicious email subject lines or shady ones. Click for BONUS, or Find the Good Stuff <<<-CLICK HERE, or SEE WHAT JOHNNY DOES TO GET THE PASSES are quite spammy, and should be avoided.
  • Attaching a huge image or attachment: Don’t attach huge files as attachments unless really required. You can always upload the file to some server or drobox etc. and provide them with the link to download if they wish to. This way you won’t become their enemy for jamming their mailbox with huge emails.
  • Not Using Professional language: While you could use the English language to the fullest, and use big words, it is not always recommended. You should write emails as if a high school kid is going to read it. Most people’s vocabulary is not huge, and they don’t read much books or learn new things beyond school years. Some experts go so far to say that you should stick to a Primary 6th grade English to be perfectly clear to everyone.
  • Not Using Formatting, Paragraphs, Headings: Do space out your emails with paragraphs, and points, or headings. Don’t write in huge block paragraphs of 8 to 10 lines or more in each block. It becomes pretty difficult to read, and you can lose a lot of people from getting your message. Use bold or underlined text in some important areas to make them stand out, but don’t bold every thing.
  • Lengthy emails That Are Not To the Point: Write only as much as you need. Don’t start a long winded email that goes on and on. We are writing an email, not a sales page.
  • Use of Emoticons: For processional emails, it is not recommended to use any emoticons. So don’t try to act cute on business emails. Stay to the point, clear, and professional as possible.

Related Training:

Learn the Art of Professional Business Email Writing & Email Etiquette in our 2 day WSQ Funded workshop.

Full of exercises and practical examples, it is a totally immersive workshop that will work wonders to your email writing skills. Do check it out here: Writing Professional Emails

Do You Know the 4 Ways to Use the Enter key in Excel?

Control Enter key combination in Excel

There are 4 different ways to use the Enter key in Excel

  1. ENTER key
  2. SHIFT + ENTER combination
  3. ALT + ENTER combination
  4. CONTROL + ENTER combination

Learning these will help you to improve your productivity and efficiency in Excel, and get the work done faster, without wasting time doing the same thing again and again.

Let’s find out How to Master the ENTER Key in Microsoft Excel

Microsoft Excel is a powerful tool for data management and analysis, and knowing how to use the ENTER key efficiently can significantly improve your productivity. In this article, we will explore four different ways to use the ENTER key in Excel: ENTER, SHIFT + ENTER, ALT + ENTER, and CTRL + ENTER.

1. Using the ENTER Key

Step-by-Step Guide

  1. Enter Data:
    • Click on any cell where you want to input data.
    • Type your data (e.g., “Sales Report”).
    • Press the ENTER key.
    • Result: The cursor moves to the cell directly below the current cell, and the data is entered into the selected cell.

Enter key in Excel

Practical Use

Use the ENTER key to quickly enter data down a column. This is useful when you have a list of items to input.

2. Using SHIFT + ENTER

Step-by-Step Guide

  1. Enter Data:
    • Click on a cell where you want to input data.
    • Type your data (e.g., “Marketing Budget”).
    • Press SHIFT + ENTER.
    • Result: The cursor moves to the cell directly above the current cell, and the data is entered into the selected cell.

Shift Enter in Excel

Practical Use

SHIFT + ENTER is helpful when you need to enter data upwards, especially useful for making corrections or additions to previously entered data in a column.

3. Using ALT + ENTER

Step-by-Step Guide

  1. Enter Data with Line Break:
    • Click on a cell where you want to input data.
    • Type the first line of your data (e.g., “First Quarter”).
    • Press ALT + ENTER to add a line break within the same cell.
    • Type the next line of data (e.g., “Sales Report”).
    • Press ENTER to finalize the entry.
    • Result: The text appears in multiple lines within the same cell.
Alt Enter key in Excel

Practical Use

Use ALT + ENTER when you need to input multiple lines of text in a single cell, such as addresses or notes.

4. Using CTRL + ENTER

Step-by-Step Guide

  1. Select Multiple Cells:
    • Click and drag to select multiple cells where you want to input the same data.
    • Type your data (e.g., “Approved”).
    • Press CTRL + ENTER.
    • Result: The same data is entered into all selected cells.
Control Enter key combination in Excel

Practical Use

CTRL + ENTER is ideal for entering the same data across a range of cells, saving time when dealing with repetitive entries.

Learn More at Intellisoft

Intellisoft provides comprehensive Microsoft Excel Training, where you can learn many more shortcuts, functions, and features of Excel. Join our hands-on workshops at our Training Centre at Fortune Centre to enhance your Excel skills and boost your productivity.

Visit Intellisoft for more details and course schedules for our WSQ Funded Excel Courses.

PMP Exam Preparation: Preview Sessions Each Month

Group of Participants attending the PMP Certification program at Intellisoft Systems Singapore

Are you an active Project Manager?

Are you looking for ways to stand out, accelerate your career and get recognition?

If you are, then you should be planning to get the most coveted Project Management Professional (PMP) Certification from PMI Inc. of USA.

It is the top qualification for Project Managers, and is very well received in Singapore. In fact, Singapore ranks as one of the Top 5 countries where the maximum number of PMPs come from.

The PMP exam is not easy. More than half the people who attempt the exam can’t pass it in their first attempt. So don’t take this lightly.

Intellisoft is holding  regular Preview Sessions where our senior PMP trainers will give you an overview of the exam.

Some of the Topics covered in the Intellisoft PMP Exam Preview Session are:

  • About the PMP Exam,
  • Eligibility criteria for the PMP Exam,
  • Methodology used in  the Intellisoft 4 Day PMP Exam Preparation Workshop (available on Weekdays or Weekends)
  • Our Trainers
  • Our exam centric teaching approach
  • WSQ Grant, eligibility, how to claim, claim % etc.
  • The Intellisoft PMP Exam Passing Percentage
  • Change to the Exam, its format,
  • Types of PMP Exam Questions, and the Right approach to answer the questions.
  • Question & Answers Session – where you can ask any questions you have, and our PMP trainers will answer them for you.

If you would like to attend the PMP Exam Preview session at Intellisoft, you can join us by sending an email to Lisha.

Intellisoft Training Schedule:
Intellisoft will be hosting Weekday and Weekend Training Programs for PMP Exam Preparations. You can check the latest PMP Training Schedule here.

Registration for the PMP Exam Prep Workshop:
You can register online for the PMP Training conducted at Intellisoft.

36 Contact Hour Certificate:
Intellisoft Systems will provide you with a 36 PDU certificate at the end of the 4th day PMP training program. This will enable you to attain Education Requirements for the PMP examination.

PMI Registered Education Provider (PMI R.E.P.)
Intellisoft Systems has been awarded the Global PMI Registered Education Provider by PMI. So you can be sure that you are getting quality PMP training and education, provided by our PMP Certified Trainers.

Simply sign up for the PMP Preview Session, and get to know more about how you can qualify and attain the PMP Certification, and boost your career with professional certification!

See you for the PMP Exam Preview Talk at Intellisoft Training Centre in Singapore!

Register Now

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Python, Power BI, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!

Found What You Were
Looking For?

Just Tell us...

We're Here To Help You!