Welcome to Excel Jet Consult Blog Post. In this tutorial, we will learn how to use the Advanced Filter in Power BI Below is the report we want to apply multiple advanced filter We want to filter the Account Manager names that start with P or S Hover the mouse right of the Account Manager in the Filters and click […]

Welcome to Excel Jet Blog Post. In this tutorial, we want to learn how to write nested IF statement in Power BI Power Query. Let’s get started In the caption above, we will create a custom column. In the custom column, Customer Type Platinum, Gold, Silver and Bronze receives 10%, 8%, 5% and 2% discount multiplied by respective Total In […]

Welcome to Excel Jet Consult. In this tutorial, we will learn how to use IF and OR Function in Excel Power Query. Let’s get started We want to create Custom Column and use IF and OR functions to return Sales Status of High when State equals LOS or Total is greater or equal to 2000 To Add a Custom Column: […]

Welcome to Excel Jet Consult. In this tutorial, we want to learn about the new feature in Power BI Power Query Editor COLUMN QUALITY Shows the quality of data preview. Light blue colour indicate that all preview data is valid. Red colour indicate that there is error in the data while the black colour indicate that there are empty cells […]

Welcome to Excel Jet Consult. In this tutorial, we will learn how to connect to relational data in the SQL Database from Power BI. Let’s get started. The first thing we wants do is open Power BI In the Power BI Desktop, click on Get Data drop-down and select SQL Server In the SQL Server database, I am going to […]

Welcome to Excel Jet blog post. In this tutorial, we will learn how to use IFS function inside Data Validation. Data Validation is a feature in Excel that allows users to control values entered in the spreadsheet. In the caption below, we want to apply List Data Validation to cells B5:B8 using lookup table to the right as the input […]

Welcome to Excel Jet Consult blog post. In this short tutorial, we want to learn how to use the elegant SUMPRODUCT function without cell reference. SUMPRODUCT returns the sum of the products of corresponding ranges or arrays SUMPRODUCT Syntax                    =SUMPRODUCT(array1, [array2], [array3], …) In cell J5, we want to calculate the Total Revenue Account Manager named Connor Rulibeth, Seafood Product1, […]

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use SUMIFS and INDIRECT Functions to perform a dynamic Total Sales. SUMIFS is a native Statistical function that adds the cells specified by a given criteria SUMIFS Syntax                  =SUMIFS(sum_range, criteria_range1, criteria1, ..) INDIRECT is a native elegant Lookup function. It delivers the reference specified by […]

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to unpivot columns in Power BI Power Query Unpivot Columns translates all but the currently unselected columns into attributes -value pairs. We want to unpivot the data in the Power BI Power Query of the caption below To unpivot other columns: Select Qtr 1 to […]

Welcome to Excel Jet Consult. In this tutorial, we will learn how to use the new MAXIFS Function with multiple criteria in VBA. Let’s get started MAXIFS Funtion returns the maximum value among cells specified by a given criteria or conditions MAXIFS Syntax                  =MAXIFS(max_range, criteria_range1, criteria1, …) Argument Max_Range – the range that contains the numeric values to evaluate Criteria_range1 […]