Sunday, 10 December 2017

How to Append Queries or Tables in Power BI

Appending One or More Queries or Tables in Power BI
When we append the Two tables, rows will be Appended/Combined into one Table, one below the another. Append will not remove the duplicate rows in the result.

Scenario 1:
Suppose we have two Tables as follows which we want to append.
Here, The CustBySegment Table has 3 Columns and the  CustByRegion has 4 Columns.


Both the Tables has 2 Matching Columns ( In Appending, Common Key Column is not mandatory).
CustByRegion Table has two columns that are not exist in the CustBySegment.
CustBySegment Table has one column that is not exist in the CustByRegion.



CustByRegion Table

CustBySegment Table

-------------------------------------------------------------------------------------------------------
Append Method : Appending CustBySegment  into CustByRegion
-------------------------------------------------------------------------------------------------------
We can do the Appending as follows..
1) Go to Query Editor > Home > Append Queries 
We will use "Append Queries as New" when we want to Append one or more Tables into a new Table. 
Here we need to select "Append Queries" as we want to Append one or more Tables into an existing Table.

Select the Primary Table "CustByRegion" into which we wants to Append a Table, and then Click on the Append Queries.


2) Next select the Table which we want to Append to Primary Table 
Select Table CustBySegment which we want to Append to Primary Table CustByRegion


Next click OK to finish the Append. 
Result :
Now see the Result in the Primary Table CustByRegion after Append as follows.
Here the 1 additional column is added as new into CustByRegion and the Matching Columns data rows from both the Tables are appeneded one below the another. 


Notes : 
If the Column Names are Matches then It will append data in one Common Column in Append Output.
If the Column Names are different then It will add as a New Column in the Append Output.
Null values will be populated to Appended Table Columns which don't have the Matching Columns in Primary Table.

If the Column Names are Matches then It will append data in one Common Column in Append Output, though there is String data in one column and Numerical data in another column as it wont bother about the data in rows.
IF we use "Append Queries as New" when the result will Append into a new Table.
-------------------------------------------------------------------------------------------------------
Scenario 2:
Suppose we have two Tables as follows which we want to append.
Here both the Tables have Two matching data columns but with difference in Column Names, as well as New Columns.

CustByRegion Table


CustBySegment Table

-------------------------------------------------------------------------------------------------------
Append Method : Appending CustBySegment into CustByRegion with Matching Data Columns but Different Column Names and New Columns
-------------------------------------------------------------------------------------------------------
Here, The CustBySegment Table has 4 Columns and the CustByRegion has 3 Columns.
Both the Tables has 2 common matching data Columns with difference in Column Names.

We can do the Appending as follows..

1) Go to Query Editor > Home > Append Queries
We will use "Append Queries as New" when we want to Append one or more Tables into a new Table.
Here we need to select "Append Queries" as we want to Append one or more Tables into an existing Table.
Select the Primary Table "CustByRegion" into which we wants to Append a Table, and then Click on the Append Queries.






2) Next select the Table which we want to Append to Primary Table 
Select Table CustBySegment which we want to Append to Primary Table CustByRegion


Next click OK to finish the Append. 
Result :
Now see the Result in the Primary Table CustByRegion after Append as follows.
Here the 3 additional columns are added as new into CustByRegion though there are Matching Data Columns(as Column Names are Different) in both the Tables.


Notes : 

If the Column Names are different though there is a matching data then It will add as a New Column in Append Output.
If the Column Names are different then It will add as a New Column in the Append Output.


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts