cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webportal Member
Member

Append two tables with different sizes

I have table one with 5 columns and table two with 7 columns.

How can I append them into a new table with only the common columns (that is, the columns with common names)?

I tried all sorts of "Append Queries" in the Query Editor but it seems that it only works with exactly identical tables.

In DAX, UNION seems to have the same limitation.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
webportal Member
Member

Re: Append two tables with different sizes

Thanks, I've figured out how appending works on Power BI desktop.

In fact, it is quite simple.

Thank you

View solution in original post

7 REPLIES 7
Sean Super Contributor
Super Contributor

Re: Append two tables with different sizes

webportal Member
Member

Re: Append two tables with different sizes

@Sean

Thanks, but I think I need to append and not merge. It seems the solution would be to harmonize the column names for both tables in the editor and then all columns will show in the appended table.

Then, I'll remove the ones I don't need.

Thanks anyway.

kcantor Super Contributor
Super Contributor

Re: Append two tables with different sizes

@webportal

Create blank columns in the table with only 5 columns to match the table with 7 columns. Append the queries then remove the unnecessary columns.

Just make sure the columns all have exactly the same column name and data type.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




CahabaData New Contributor
New Contributor

Re: Append two tables with different sizes

One approach: use the New Table feature and only include the columns wanted.  Call this Table V2.  Maybe you have to do that for both - not sure.  Then append one to the other.

 

 

www.CahabaData.com
Moderator v-sihou-msft
Moderator

Re: Append two tables with different sizes

@webportal

 

In Power BI Desktop, when using "Append Queries", if two tables have different number of columns, it will append all columns together and generate columns of blank values for the table with less columns. It's "CROSS" appending. If you want to do "INNER" appending. You have to copy Table2 and only keep 5 columns, then append with Table1.

 

Regards,

Highlighted
webportal Member
Member

Re: Append two tables with different sizes

Thanks, I've figured out how appending works on Power BI desktop.

In fact, it is quite simple.

Thank you

View solution in original post

jdriscol Regular Visitor
Regular Visitor

Re: Append two tables with different sizes

Can you elaborate how you did this in BI? Thanks.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 147 members 2,012 guests
Please welcome our newest community members: