Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@Anonymous
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
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]
)
)
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 |
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 |
@Anonymous
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
You can use DAX Studio to export your data.
Or you can use Excel to export an entire table. See the article below
@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
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
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
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
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