Fixing Improper Dates in Power Query

Welcome to Excel Jet Consult blog post. In this tutorial, I will share with us how to solve the problem of improper date in Power Query. Let’s get started Quite often, I do get questions from people who have challenge of working with dataset that wasn’t correctly entered on Excel. Hence, I write this blog post to address the problem. […]

Continue reading

SUMIFS/INDEX/MATCH VS. SUMPRODUCT Functions: HR Data Example

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how we can use SUMIFS/INDEX/MATCH and SUMPRODUCT differently to achieve the same result. Let’s get started In the captions below, we have HR Salary Data in Dynamic SUMPRODUCT and Dynamic SUMIFS sheet Note, we have list data validation in cells M2 (List of unique department), and […]

Continue reading

Excel Office 365: Ideas

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the new Ideas tool in Excel available to Office 365 users. The Ideas enables users to have a deeper understanding of data through high-level visual and summaries, trends and patterns. The Ideas tool is in the Home tab as seen in the caption below […]

Continue reading

DAYS Function

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn about the DAYS function and how we can use it to calculate dates between two dates. Let’s get started DAYS Function returns the number of dates between two days DAYS Syntax                                                       =DAYS(end_date, start_date) In the caption below, we want to calculate how man days that is in […]

Continue reading

Rolling Average In Power BI Power Query

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to dynamically calculate Rolling Average in Power BI Power Query. Let’s get started. Statistically, Rolling Average or Running Average is a calculation that analyzes data points by creating a series of averages of different subsets of a comprehensive data set. In the caption below, we have […]

Continue reading