Text Modifications with Power Query in Excel

Excel – Types of Merging of Queries in Power Query

Last Updated: 29 Dec, 2021

A

Asha Senthilkumar

Follow

In Power Query, we have an option to Combine two tables using “Merge queries”.

Merge Queries: Merge with current query with another query
Merge Queries as New: Merge with current query with another query and create New query

In this article, we explore different types of merging of queries in Power Query with two tables. We will combine two years of sales data (2020 and 2021) with the key field “Cust_ID”.

Implementation:

Follow the below steps to implement merging of queries:

Step 1: Open Power Query Editor.

Step 2: Select your Query “Sales_2020”. Go to Home >> Merge Queries >> Merge Queries as New.

We have six type of joins:

Left Outer (all from first, matching from second)
Right Outer (all from second, matching from first)
Full Outer (all rows from both)
Inner (Only Matching rows)
Left Anti (rows only in first)
Right Anti (rows only in second)

Step 3: Select Second table; Select key fields “Cust_ID” in both tables; Select Left Outer (all from first, matching from second) and Press “OK”.

Step 4: Above step create a new query “Merge 1” as below in your power query.

Note: Sales_2021 is not a text value it is a table for each row.

Step 5: Click “Sales_2021” drop down icon; Make sure Expand is selected. Press “OK”.

Now merged table with all columns.

Step 6: Change query name “Left Outer”.

Step 7: Press “Close & Load”, It will add a new sheet “Left Outer” with a Merge table.

Output:

Left Outer [All records from Sales_2020 and matched records from Sales_2021]:

Select different types of joins in step 3 and generate respective merge tables

Right Outer [All records from Sales_2021 and matched records from Sales_2020]

Full Outer [All records from both Sales_2020 and Sales_2021]

Inner [Only matched records from both Sales_2020 and Sales_2021]

Left Anti [Un-matched records from Sales_2020]

Right Anti [Un-matched records from Sales_2021]

Article Tags :

A

Asha Senthilkumar

Follow

News

Discuss

Do you want to advertise with us?Click here to know more

A

Asha Senthilkumar

Follow

Excel - Types of Merging of Queries in Power Query

article_img

In Power Query, we have an option to Combine two tables using “Merge queries”. Merge Queries: Merge with current query with another queryMerge Queries as New: Merge with current query with another query and create New query In this article, we explore different types of merging of queries in Power Query with two tables. We will combine two years of sales data (2020 and 2021) with the key field “Cust_ID”. Implementation: Follow the below steps to implement merging of queries: Step 1: Open Power Query Editor. Step 2: Select your Query “Sales_2020”. Go to Home >> Merge Queries >> Merge Queries as New. We have six type of joins: Left Outer (all from first, matching from second)Right Outer (all from second, matching from first)Full Outer (all rows from both)Inner (Only Matching rows)Left Anti (rows only in first)Right Anti (rows only in second) Step 3: Select Second table; Select key fields “Cust_ID” in both tables; Select Left Outer (all from first, matching from second) and Press “OK”. Step 4: Above step create a new query “Merge 1” as below in your power query. Note: Sales_2021 is not a text value it is a table for each row. Step 5: Click “Sales_2021” drop down icon; M

G

Gautam goel

Follow

How to Create a Relative File Path with Power Query in Excel?

article_img

In today's digital environment, sharing Excel files effectively is vital. A common issue is that Excel's Power Query uses absolute file paths, which can break when files are opened on different systems. To enhance portability and maintain data connections, it's crucial to convert these absolute paths to relative paths. This article explains how to make Excel workbooks more shareable by using Power Query to switch from absolute to relative file paths. How to Create a Relative File Path with Power Query in Excel?Why Convert Absolute Paths to Relative Paths?Problem with Absolute Path: Absolute paths can cause issues when sharing files because the path is specific to the original user's computer. Due, to this file sharing, is a big and significant issue. For example, an analyst is analyzing data in its intelligence tools source via power query from your PC, the tools work well until the files are in your PC, but stop working as soon as they are imported into the third person PC, this happens because the absolute path in the third person computer might be different, and our power query files are still searching for the old absolute path provided in my computer. To resolve this issue, on

C

Chetan

Follow

Date Modifications with Power Query in Excel

A data preparation and transformation engine is Power Query. Power Query includes a Power Query Editor for implementing transformations as well as a graphical interface for obtaining data from sources. With Power Query (also referred to as Get & Transform in Excel), you may import or connect to external data and then modify that data to match your needs, for instance, by combining tables, altering the data type, or eliminating a column. When you're ready to produce charts and reports, load your query into Excel. You may periodically update the data by refreshing it. Steps to Open Power Query in Excel Step 1: First create a table in Excel. Step 2: Select the field and then navigate to the data tab on the top of the ribbon. Then select the Get data option and choose From other sources and select From Table/Range option. Step 3: Then Create Table dialog box appears. Here select the table range and then click OK. Step 4: Now you can see it converts the excel table to a power query Editor. Date modification with Power QueryAge The number of days between the specified date and the current date is determined when you choose the Age option and choose a date column. Next, you mu

A

Ankur Pal

Follow

Text Modifications with Power Query in Excel

article_img

In Excel, Power Query is a business intelligence tool that is used to analyze the data imported from different sources. With the help of power query, we can easily clean, transform and reshape our data as per requirement along the way of analyzing the data. In this example, we will learn about the Text Functions which can be easily used with the help of Power BI for the modification of the data. Given below are the formatting functions that can be used to create and manipulate the text values. Function Name Description Trim It is used to trim(remove) the whitespace from both ends of the text.Clean It removes the non-printable characters from the text and returns it.Merge Columns It is used to combine two or more columns into a single column using a separator.Lowercase It is used to make text lowercase.Uppercase It is used to make text uppercase.Add Prefix It is used to add prefixes to a cell or set of cells.Add Suffix It is used to add suffixes to a cell or set of cells.Capitalize Each Word It is used to change a text into proper text.Text Modifications with Power Query Step 1: Create a Database. In order to use the text operations, we will be using the following random names. Fig

C

Chetan

Follow

How to Add a Conditional Column in Power Query in Excel?

We may use Power Query to generate new columns whose values depend on one or more criteria that have been applied to existing columns in your database. The condition imposed on an existing column in the data model serves as the foundation for Power BI's conditional column. On the Add column tab, in the General category, is the Conditional column command. In this article, we are going to discuss the Conditional column in Power Query and in Power Pivot queries. Step By Step Implementation Step 1: Create an excel table with fields Roll No., Student name, and Marks. Step 2: Select a table. Then navigate to the data tab on the top of the ribbon and select the Get Data option and then go to from other sources and choose from Table/range. Step 3: Then create a Table dialog box appear. Here select the range and check my table has headers then click on OK. Step 4: Now as you can see the excel table is converted to a power query editor. Step 5: Now in the power query go to the add column tab on the top of the ribbon and here select the conditional column. Step 6: Add Conditional Column dialog box appears. Here write the new column name and then apply the if condition. Step 7: Her

D

Dhatri

Follow

How to Import, Edit, Load and Consolidate Data in Excel Power Query?

article_img

Power Query is an easy and efficient way of solving simple data tasks. Most of our valuable time is frequently consumed by tedious manual procedures like cut and paste, column merging, and filtering. These operations are greatly simplified with the Power Query tool. A further advantage is that, in comparison to other BI tools, Power Query is simple to use. The Power Query user interface is straightforward. Given how much it resembles the Excel interface, many users will find it to be familiar. What is Power Query? Power Query is an application for preparing and transforming data. Using Power Query, you may perform transformations to data obtained from sources using a Power Query Editor and a graphical user interface. We can import data from a variety of sources, clean it, convert it, and then reshape it to suit our needs using Power Query, a business intelligence tool provided by Microsoft Excel. Power Query allows us to create a query just once and reuse it later by simply refreshing it. Different Ways to Import Data to the Excel Sheet Excel allows importing data from a variety of sources. We will look at each of these in detail. While importing data, power query has several feat

C

Chinmay Mundhada

Follow

How to use Power Query as a Pivot Table Data Source?

article_img

Power Query is a tool that is available in Microsoft Excel that simplifies the process of importing data from different sources and sorting them into an Excel sheet in the most convenient and usable format. It is a tool in Microsoft Excel that is used to simplify the process of importing data from different source files and sorting them into an Excel sheet. It also removes the need to do the same filtering techniques to transform the same data set at different points as per requirement; the user only needs to set up a query, that is, the rules for sorting, once, and refresh the query every time you need to perform a particular action. So, you can easily use a power query once and simplify your data rather than using it again and again. The biggest advantage you get when you use the Power Query is higher efficiency. A pivot table is aggregated value such as the sum, average, minimum, and maximum of a large dataset to represent the dataset in a concise manner. Pivot tables are generally used to represent the Summary of large datasets. The input of Power Query effectively helps to put large datasets in the proper format for the pivot tables to process data. Steps to implement Power Qu

R

Rohit Sarkar

Follow

How to Merge Content of All Files in Folder with Power Query?

article_img

Use Power Query to create a single table from numerous files with the same schema that are saved in the same folder. For instance, you could wish to merge budget workbooks from several departments each month when the columns are the same but the workbooks have different numbers of rows and values. Once it is configured, you may add further transformations, just as you would with any other single imported data source, and then refresh the data to view the results for each month. Merging Content of All Files Suppose we have multiple files in a folder that contains consistent types of data, with the help of power query we can merge all the data together with the help of power query easily. Let's say there is a folder "Student" that contains several files that contain information about the students in the form of a table. We need to merge all the student details from all the files into a single file. As far as all the files are consistent, which means hold similar kind of tables only, merging all the details are very easy. Steps to Merge Content of All Files in Folder with Power Query Step 1: Open Excel and then go to the Power Query on the top of the ribbon and then select From File

R

RISHAB NIGAM

Follow

Power Query – Source Reference as File Path in Cell

article_img

Power query helps in doing automation in an efficient manner. It allows users to utilize files stored in specific locations and apply routine transformation steps on those files. It allows users to embed file paths and file sources in an Excel cell. The end user can make use of named ranges and Excel cells as references to access multiple files. In this article, we will learn how to refer to Source as a file path in a cell. Adding Source Reference as File Path in Cell Let us take a basic example of how to make use of Excel cells in manipulating file paths. Below is an example where 2 Excel files are there, Power query1.xlsx and Power query2.xlsx. The reference to source as file path, is only valid if the excel files, have same table name, and same number of columns in it. For example, Power query1.xlsx and Power query2.xlsx, has same table name(Table_src) and same number of columns. Step 1: In the above example, Excel cells B2 and B3 contain the path of the Excel file. Let us bring the data to be used for transformation. The name of cell B2, should be replaced with FileP, as shown in the image below. FileP will be the name range cell of B2. Step 2: To do this, go to the Data ri

N

Nikitha Sri

Follow

Editing Excel Macros in Excel

article_img

Excel Macro is a set of actions that can be recorded, saved, used multiple times. This feature saves us a lot of time when dealing with repetitive tasks and huge data sets. We can always make changes to an existing Macro in Excel. There are two ways to record and run macros: Excel Commands Excel Visual Basic for Applications(VBA).1. Copying a Macro Code We can reuse the existing macro code while creating new Macros. For example, if you have existing macros created using the record function, we can reuse the code to write new macros. To open the code follow the below steps: Select Tools --> Macros --> Visual Basic Editor. Copy code from the existing macros(use Command + C) and paste(use Command + V) in the new macro2. Renaming a Macro Follow the below steps to rename a Macro in Excel: Select Tools --> Macros --> Visual Basic Editor. Select name on the left plane.Edit the name.3. Deleting a Macro Follow the below steps to delete a macro in Excel: Select Tools --> Macros --> Macros... Select the macro to be deleted. Click on the minus(-) symbol at the bottom.Select yes to delete the macro.