Join Types in Power Query – Part 2: Lookup Dashboard
Excel Unplugged
by Gašper Kamenšek
2y ago
We love Joins and we hope you do too! If you’re not there yet, you should really check Part 1 of this series and learn why Power Query Join is as cool as a cold summer breeze. We looked at all the join types  in Power Query and what they can tell us. Ready to take things further? You’re in the right place: in this Part 2 we’ll use the different join types create a LOOKUP Dashboard, which will be a single table, but also a concept you can develop onwards. Starting data Our data will consist of two tables called HR and System. You can find the file we used here. Table records can be matched ..read more
Visit website
Join Types in Power Query – Part 1: Join Types
Excel Unplugged
by Gašper Kamenšek
2y ago
Who doesn’t love Join? It’s like Excel’s LOOKUP, but better! I use Join all the time for merging tables one way or the other. Let’s look at all the different Join types in Power Query. And then we’ll take things further in Part 2: we’ll create a LOOKUP Dashboard, which will be a single table, but also a concept you can develop onwards. Join Types What are all the possible join types? We’ll look at the following: Left Outer Join Right Outer Join Full Outer Join Inner Join Left Anti Join Right Anti Join So if we represent left and right table T1 and T2 graphically, joins would look unions on t ..read more
Visit website
Why You Should Know Power Query – Part 3: Merge
Excel Unplugged
by Gašper Kamenšek
2y ago
Power Query is a great tool for data transformation. I often use as an upgrade to Excel. We already covered two great functionalities in first two parts: fill and split. Let’s look at another one: Power Query merge or join or lookup, or whatever you want to call it. We have two tables and we would like to match it up with another table based on a common column. How-to Let’s look at the following two tables. We’ll call them Main table and Lookup table. Notice there are multiple repeating IDs in the Lookup table (on the right) and only unique ID values in our Main table (left). We would like to ..read more
Visit website
Why You Should Know Power Query – Part 2: Split
Excel Unplugged
by Gašper Kamenšek
2y ago
Power Query can be more than an ETL tool – I often use it as an upgrade to Excel. It has some neat functionalities that make transforming data so much more simpler. In this article we’ll look at Split. Splitting is a common task in Excel. In simple examples you would use Text to Columns command. But what about if you wanted to split/extract address number in a not-so-simple table? Or if you wanted to split to rows? You would need to use split in Power Query. How-to Let’s look at the following table. We would like to get a column of address numbers. There are several ways to do this in Excel ..read more
Visit website
Why You Should Know Power Query – Part 1: Fill
Excel Unplugged
by Gašper Kamenšek
2y ago
Power Query is meant to be an ETL tool, but I often find myself using it in addition to Excel. Power Query has some neat functionalities that make transforming data a lovely, summer breeze – like experience. We’ll look at three such functionalities: fill, split and lookup. In this article we’ll cover the fill feature in Power Query. How-to We use fill down very often. It works well in simple cases, but let’s say I have two values I want filled down, like in the example below. Select the area you want filled. Then select Editing > Fill > Fill Down. The result below is not what we wante ..read more
Visit website
Data Types – Part 2 – Create Your Own Data Types in Power BI
Excel Unplugged
by ExcelUnplugged
2y ago
Let’s see how to create your own data types in Power BI! This is a follow up of Part 1, where we learned how it’s done in Power Query. How-to We start by loading the following Excel tables in Power BI. We won’t pay much attention to their contents, we’re only interested in ExcelOlympicsProducts table, which is the same table as in Part 1. Power BI recognizes the identical column names and types and automagically creates connections, so we get a pre-made model. We could now go to report mode and build from there, but we’re not going to. We want to make ExcelOlympicsProducts table organizational ..read more
Visit website
Data Types – Part 1 – Create Your Own Data Types in Power Query
Excel Unplugged
by ExcelUnplugged
2y ago
How can you create your own data types in Excel? Oh yes, you can do it! In this article, we’ll learn how you can create data types in Power Query. Follow the tutorial below. If you don’t know about data types, we recommend you first watch an introductory video here. How – to We’ll start with the following tables: the first is the Product table. And second is the Sales table. To load to Power Query, select the Sales table and go to Data > From Sheet. Repeat for the Product table. We now have both tables in Power Query. Select ProductID column in Sales table and then Home > MergeQuerie ..read more
Visit website
Remove Blank Rows in Excel
Excel Unplugged
by Gašper Kamenšek
2y ago
How to remove blank rows in Excel? Let’s look at some examples for simple and complicated tables. Simple Table Let’s start with the following table. Starting table The table contains clean empty rows which we would like to remove. Blank row We select these empty cells by using Home > Find & Select > Go To > Special. Selecting blank cells A Go To Special menu opens up where we select Blanks. Confirm with OK. Selected blank cells We now delete the selected empty rows with Delete and –. Or, we can use Home > Delete > Delete Table Rows. By using filters Another way is to use fil ..read more
Visit website
AI in Excel – Part 3 – Analyze Data, the Future of Data Analysis?
Excel Unplugged
by Gašper Kamenšek
2y ago
Welcome back to Artificial Intelligence in Excel! In Part 1, we looked at a feature called Flash Fill. It works by recognizing patterns of data and filling out the remainder. In Part 2, we introduced Power Query’s Column From Examples command. It’s similar to Flash Fill. It fills in the rows based on the input we provide. However, it has a great advantage over Flash Fill, It’s dynamic. If the input data changes, the filled-in data changes too! In Part 3, we’ll get to know Analyze Data. It’s a powerful feature that provides high-level summaries, visuals in the form of different charts that show ..read more
Visit website
AI in Excel – Part 2 – Column From Examples
Excel Unplugged
by Gašper Kamenšek
2y ago
Welcome back to Artificial Intelligence in Excel! In Part 1, we’ll looked at a feature called Flash Fill. It works by recognizing patterns of data and filling out the remainder. In Part 2, we’ll look Power Query’s Column From Examples command. It’s similar to Flash Fill: it fills in the rows based on the input we provide. However, it has a great advantage over Flash Fill: it’s dynamic. Column from Examples command actually constructs a formula based on the initial data. If the input data changes, the filled in data changes too! Let’s see how it works. Column From Examples command Let’s start w ..read more
Visit website

Follow Excel Unplugged on FeedSpot

Continue with Google
Continue with Apple
OR