cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
achandrashekara Frequent Visitor
Frequent Visitor

Combining 2 Columns from 2 tables into One Column as a result of new table

I have 2 Tables

 

Table 1

 

Color -Field Name

Black

Blue

Gree

Yellow

 

Table 2

 

Color - Field Name

Black

Blue

Orange

Green

White

 

I want to combine Color column from both Table 1 and 2 and put it in Table 3 without removing duplicates ,like

 

Table 3

 

Color -Field Name

Black

Blue

Gree

Yellow

Black

Blue

Orange

Green

White

1 ACCEPTED SOLUTION

Accepted Solutions
achandrashekara Frequent Visitor
Frequent Visitor

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

@arndtvissing 

@Pauwnrajpp 

@Nishantjain 

 

Thank you all for your quick support. All of your options are working for me but my export options are limited to 30K rows in power BI desktop. When i tried to export the data from power BI online i noticed that i dont have access to export data in Underlying data mode. Once again thanks for all your help.

 

Thanks,

Chandru

 

8 REPLIES 8
Pauwnrajpp Member
Member

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

Hi achandrasekhara,

 

First, if you use SQL tables, then you shoud apply full outer join to combine both the columns from the tables and create a dataset in Power BI.

 

Second, if you Datasource is like Excel,etc.. then you can append both the dataset in Power BI query editor.

 

The above two methods will provide you the result without removing duplicates.

 

Regards,

Pradeep

Nishantjain Member
Member

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

@achandrashekara 

 

In addtion to suggestions from @Pauwnrajpp you can also do the following:

 

- In Power Query mode, use the append feature Power Query - Append

- Using dax you can create new table Dax - Calculated tables

 

Thanks

Nishant

achandrashekara Frequent Visitor
Frequent Visitor

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

@Nishantjain 

@Pauwnrajpp 

 

Hi Both,

 

I am not getting that what i want. i have attached a spreadsheet with data for your reference.

 

Worksheet("Color 1") contains 10K + rows,

Worksheet("Color 2") contains 160K + rows and Worksheet("Output") contains all rows values that is 170K.

 

But when i did the full join i am not getting all of the rows from both table instead i am getting only 4K + rows.

 

Is there any way to deal with it.

 

Thanks,

Chandru

achandrashekara Frequent Visitor
Frequent Visitor

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

@Nishantjain 

@Pauwnrajpp 

 

I forgot to give a reference file.

 

The below link is for the data which has Sheets Color 1 and Color 2. I want to combine rows from them and put it in new table.

https://docs.google.com/spreadsheets/d/12Bihvjz-XwGe-zeJC1AwL2niJ-W0fTyDFX2b_jbr1aA/edit?usp=sharing

 

Thanks for your help.

 

Thanks,

Chandru

 

 

 

Pauwnrajpp Member
Member

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

Hi Chandru,

 

Sorry that the link is not opening for me.

 

Append query should work in your case. Remove joins if you already defined in between these 2 tables and append.

When you append these objects, just be ensure that you are keeping nulls in both the objects. 

 

And to check before, don't apply any filters on top of it. Remove all the filters and check you have your data appended.

 

Regards,

Pradeep

arndtvissing Frequent Visitor
Frequent Visitor

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

you can use Dax to create a common table.

Something along the line of 

 

=UNION(SUMMARIZE(Table1;

'Table1'[Color - Field Name]

)

SUMMARIZE(Table2;

'Table2'[Color - Field Name]

)

)

achandrashekara Frequent Visitor
Frequent Visitor

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

@arndtvissing 

@Pauwnrajpp 

@Nishantjain 

 

Thank you all for your quick support. All of your options are working for me but my export options are limited to 30K rows in power BI desktop. When i tried to export the data from power BI online i noticed that i dont have access to export data in Underlying data mode. Once again thanks for all your help.

 

Thanks,

Chandru

 

Nishantjain Member
Member

Re: Combining 2 Columns from 2 tables into One Column as a result of new table

You can use DAX Studio to export your data. 

 

Or you can use Excel to export an entire table. See the article below

 

Creating Excel “Data Dump” Reports From Power BI