cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Frequent Visitor

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

@arndtvissing 

@Anonymous 

@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

 

View solution in original post

9 REPLIES 9
Highlighted
Anonymous
Not applicable

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

Highlighted
Responsive Resident
Responsive Resident

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

@achandrashekara 

 

In addtion to suggestions from @Anonymous 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

Highlighted
Frequent Visitor

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

@Nishantjain 

@Anonymous 

 

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

Highlighted
Frequent Visitor

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

@Nishantjain 

@Anonymous 

 

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

 

 

 

Anonymous
Not applicable

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

Highlighted
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]

)

)

Highlighted
Frequent Visitor

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

@arndtvissing 

@Anonymous 

@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

 

View solution in original post

Highlighted
Responsive Resident
Responsive Resident

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

Highlighted
Helper III
Helper III

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

 
I have created two new columns [2016-2019 till Jun Avg] and [2016-2019 after Jun Avg] in the reports section using DAX in below table.
 
Note:WEEK is the original column from dataset in below table
 
What I have currently:
 
WEEK 2016-2017 till Jun Avg  2016-2017 after Jun Avg
2016-06-W130 
2016-06-W240 
2016-07-W1 35
2016-07-W2 50
2017-06-W130 
2017-O6-W240 
2017-07-W1 35
2017-07-W2 50
 
Now I am trying to create a new table by combining these two columns [2016-2017 till Jun Avg] and [2016-2017 after Jun Avg] as shown below.
 
What I need:
 
WEEK  2 year Avg
2016-06-W130
2016-06-W240
2016-07-W135
2016-07-W250
2017-06-W130
2017-O6-W240
2017-07-W135
2017-07-W250
 
I tried creating a new table using below DAX but was not able to achieve the above result.
 
Table = UNION(SUMMARIZE('Weekly WAMR vs Pumpage';
'Weekly WAMR vs Pumpage'[2016-2019 till Jun Avg]
)
SUMMARIZE('Weekly WAMR vs Pumpage';
'Weekly WAMR vs Pumpage'[2016-2019 after Jun Avg]]
)
)
 
 
Please help me with this.
 
Thank you!
 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors