Sunday, December 10, 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 & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog