Merging tables is a common operation in Power BI, and it allows you to combine data from multiple sources into a single table for analysis. To merge these tables, you will create a calculated column to merge the data from two or more tables into a new table.
There are several reasons why you might want to merge columns such as combining related data, creating meaningful column names, etc.
In this article, I will walk through the steps to merge two tables using a calculated column in Power BI.
Let’s get started
Introduction
You can follow these steps to merge two tables using a calculated column in Power BI:
Import the Data into Power BI
The first thing to do is to open your Power BI Desktop and import the data you want to merge into the data model by clicking on Get Data in the Home tab, then select the data source from which you want to merge tables.
Make sure that the tables have a common column that you can use to merge them.
Create a calculated column in the first table
Go to the “Fields” pane and select the first table. Right-click on the table and select New Column. In the formula bar, enter a formula that uses a common column to look up values in the second table.
For example, if the two tables have a common column called ID, you can create a calculated column in the first table like this:
Merged = LOOKUPVALUE(SecondTable[Value], SecondTable[ID], FirstTable[ID])This formula uses the LOOKUPVALUE function to look up the Value column in the second table where the ID column matches the ID column in the first table.
Merge the tables
Now, to merge the tables, go to the Home tab and click on Combine Queries. Then select Merge Queries from the dropdown list.
In the Merge Queries dialog box, select the first table as the primary table and the second table as the related table. Then choose the Merged column as the join column. Finally, select the type of join you want to perform (inner, left, right, or full outer).
Choose the columns you want to keep
Select the columns you want to include in the merged table. You can select columns from both tables, or just select columns from one table. Then click OK to merge the tables after selecting the columns you want to keep.
Power BI will create a new table that combines the data from the two tables based on the common column.
Use the merged table in your report
Automatically, the merged table will appear in the Fields pane, and you can use it in your Power BI report. To do this, just drag and drop the columns from the merged table into the report canvas, and adjust as you like.
FAQs
Can you use the CONCATENATE function to merge tables in Power BI?
No, you cannot. You can only use the CONCATENATE function to join two or more text strings together.
Can you merge more than two tables at once?
Yes, you can.
Conclusion
Merging tables using a calculated column is a powerful feature in Power BI that allows you to combine data from multiple sources in a single table.
By following these steps and creating a calculated column that combines information from two or more columns, you can merge tables and create more insightful visualizations for your data analysis.
I hope you enjoyed reading this article.
Thanks for reading!