Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to fully download and install Microsoft SQL Server 2017. Let’s get started. DOWNLOAD SQL SERVER 2017 To download SQL Server 2017, type in download Microsoft SQL Server on google search. Click on the first search result In the caption below, click on the Developer edition Download […]
Continue reading
Conventional Subtotalling in Excel and VBA
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to perform subtotal and the alternative way to perform the same operation using VBA. Let’s get started SUBTOTAL IN CONVENTIONAL EXCEL METHOD In the caption below, we have our dataset in Subtotal sheet tab We want to calculate the Net Salary for each of the Department […]
Continue reading
Using GIF and Logo Backgrounds in Power BI
Welcome to Excel Jet Consult blog post. In this tutorial, we want to see how we can use GIF and logo in as Backgrounds Using GIF and logo as backgrounds is up to every Power BI users. Depending on wat you are working on, you might want to insert logo and GIF backgrounds to our reports. In the captions below, […]
Continue reading
VLOOKUP in Excel and Power Query PBI
Welcome to Excel Jet Consult. In this tutorial, we want to look at how to perform vertical lookup in Excel and Power Query PBI. Let’s get started VLOOKUP in Excel VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from a column specified. VLOOKUP Syntax =VLOOKUP(lookup_value, table_array, […]
Continue reading
Understand One-To-Many Relationships in Power BI
Welcome to Excel Jet Consult blog post. In this tutorial, we want to take a cursory look at one-to-many relationships in Power BI. Creating relationships across imported multiple tables is important to perform accurate and reliable and easy-to-understand analysis for sound decision making. By default, Power BI is equipped to perform automatic relationship detection and creation between fact and dimension […]
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
Distinct Count In Pivot Table: Sales Example
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to count distinctive values in Pivot Table. Let’s get started In the caption below, we have a data set of 1040 transaction records. In addition, the data set is formatted in Excel Table using CTRL + T We want to ascertain how many unique Customers purchased […]
Continue reading
Dynamic Loan Amortization Schedule
Welcome to Excel Jet Consult blog post. In this video tutorial, I demonstrated how to prepare dynamic loan amortization schedule. Do enjoy the short video
Continue reading
Extract Leftmost and Rightmost Names using PQ M Functions
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the Power Query M Function to extract First and Last Name from Full Name. Let’s get started In the caption below, I have simple data in my Power Query. I want to extract the leftmost names i.e Phoebe, Tina Abiola etc to be my […]
Continue reading
COUNTROWS DAX Function
Welcome to Excel Jet Consult bog post. In this tutorial, we will learn how about the COUNTROWS DAX function. Let’s get started COUNTROWS Function counts the number of rows in a table COUNTROWS Syntax =COUNTROWS(table) In the caption below, we have our fact table named Transaction_tbl; dimension table named Dim_tbl and calendar dimension table named DimDate. […]
Continue reading