Welcome to Excel Jet Consult blog post. In this Excel video, we learn how to use the Power Query to combine multiple tables from multiple sheets and using SUMIFS & PivotTables to create sales report by country!
Continue readingTag Archives: Power Query
Power Query: Calculate Age in Years and Months
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to calculate Age in years and month in Power Query. Let’s get started In the caption below, we have our data loaded into the Power Query editor In From Date & Time group of the Add Column tab, click on Date drop-down and select Age In […]
Continue readingPower Query: Split Date and Time into Separate Columns
Welcome to Excel Jet Consult blog. In this tutorial, we want to learn how to split Date and Time using Power Query. Let’s get started. In the caption below, we have a Date & Time data in column A with Date Format: mm/dd/yyyy Format Data as Excel Table Since we want to work with Power Query, the first thing we […]
Continue readingFixing 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 readingVLOOKUP 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 readingUnderstand 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 readingRolling 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 readingPower BI Power Query: Extract Data Using Column From Examples
Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to extracting data using Column by example feature in Power BI Power Query otherwise known as Flash Fill in Excel. However, before we see how to do this in Power BI, we are going to use the same data and extract data in Excel using flash […]
Continue reading