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

[Anonymous]

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

[arndtvissing]

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]

)

)

[Anonymous]

Hi @arndtvissing @Anonymous @Nishantjain 

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-W1	30
2016-06-W2	40
2016-07-W1		35
2016-07-W2		50
2017-06-W1	30
2017-O6-W2	40
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-W1	30
2016-06-W2	40
2016-07-W1	35
2016-07-W2	50
2017-06-W1	30
2017-O6-W2	40
2017-07-W1	35
2017-07-W2	50

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!

[Anonymous · Accepted solution]

@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

[Nishantjain]

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

[Nishantjain]

@Anonymous 

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

[Anonymous]

@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

[Anonymous]

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

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

[Anonymous]

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