Welcome to Excel Jet Consult blog post. In this Part 2, I demo how to, “Part 2: Repeat All Products By Units Sold Frequency Distribution using Power Query.” Le’ts get started Video Sample Data We are going to use the same dataset used in the formula based solution (Part 1) as seen below Power Query Operation The first thing we […]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to,”Repeal All Products By Units Sold Frequency Distribution using LET | INDEX MATCH & XLOOKUP Functions.” Let’s dive in Video   Sample Data The screenshot below shows a simple dataset that is formatted as an Excel Tables using CTRL + T keyboard shortcut. We’ve […]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to create simple and dynamic charts in Excel. Let’s get started Video   Sample Data In the screenshot below, we’ve got sales report showing the Total Sales by Month and Products. In cell C15, we’ve got dropdown list data validation that houses all the […]

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to write a formula to count the number of names in cell. Let’s get started   SAMPLE DATA In the screenshot below, we have a single column that contains different length of names. Our goal is to use formula to count the number of names in […]

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to transform data using Power Query Column From Examples. Let’s get started SAMPLE DATA In the screenshot below, we have a single column of Emp Name. Our goal is to transform the name such that the last name will come first with a comma while the […]

Welcome to Excel Jet Consult blog post. In this blog post, I will show us how to combine the VSTACK and the TAKE Functions to stack data together. Let’s get started. SAMPLE DATA The screenshot below shows the transaction data with a handful of columns VSTACK FUNCTION The VSTACK Function appends arrays vertically and in sequence to return a larger array. […]

Welcome to Excel Jet Consult blog post. In this video, I demonstrated how to write advanced SQL Query in Power Query of Excel using ODBC.Query M Function. Video Set Up ODBC Connectiviity For this operation, we are required to set up the ODBC driver on our local machine. To that, you can watch this video below: Odbc.Query The ODBC.QUERY Returns […]

Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to calculate and return the Top Botton Nth Products by Total using VSTACK LET SEQUENCE XLOOKUP and LARGE Functions. Let’s get started. Video Sample Data The screenshot below shows the sample data with three columns. In addition, we’ve got a dropdown list data validation in […]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to combine multiple text file Data using Power Query in Excel. Let’s get started   SAMPLE DATASET The screenshot below shows all the 4 text files that we want to combine. They are located in the Sales Data Folder COMBINE THE TEXT FILES To […]

Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to retrieve day of the week from date value using Custom Formatting. Let’s get started Sample Dataset The screenshot below shows the dataset in columns A and B Retrieve Day From Date Values Based on the sample dataset screenshot above, it can be seen […]