Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I am looking for a solution to below problem and would highly appreciate
the solution to this.
Is it possible to combine two excel's files with different format.
For example :
File 1 :
A | B | C | |
1 | Order N | Line N | Delivery Date |
2 | L12 | 1 | 15/01/2017 |
3 | L12 | 7 | 17/02/2017 |
4 | L13 | 1 | 02/03/2017 |
5 | L14 | 2 | 20/03/2017 |
6 | L15 | 4 | 19/05/2017 |
7 | L15 | 5 | 01/02/2017 |
8 | L14 | 3 | 01/02/2017 |
9 | L20 | 1 | 08/08/2017 |
File 2
A | B | C | D | |
1 | Order N | Line N | Deadline | Delivery Date |
2 | L12 | 1 | 20/08/2017 | 21/08/2017 |
3 | L12 | 7 | 15/08/2017 | 22/08/2017 |
4 | L13 | 1 | 22/08/2017 | 23/08/2017 |
5 | L14 | 3 | 21/08/2017 | 24/08/2017 |
6 | L11 | 2 | 23/08/2017 | 25/08/2017 |
7 | L15 | 4 | ||
8 | L15 | 5 | 19/08/2017 | 24/08/2017 |
9 | L14 | 5 | 19/08/2017 | 24/08/2017 |
10 | L1 | 2 | ||
11 | L21 | 2 | 30/07/2017 | 01/08/2017 |
12 | L30 | 1 | ||
13 | L12 | 1 | 23/08/2017 | 24/08/2017 |
And after combinining, have a table result :
Order N | Line N | Delivery Date |
L12 | 1 | 15/01/2017 |
L12 | 7 | 17/02/2017 |
L13 | 1 | 02/03/2017 |
L14 | 2 | 20/03/2017 |
L15 | 4 | 19/05/2017 |
L15 | 5 | 01/02/2017 |
L14 | 3 | 01/02/2017 |
L20 | 1 | 08/08/2017 |
L12 | 1 | 21/08/2017 |
L12 | 7 | 22/08/2017 |
L13 | 1 | 23/08/2017 |
L14 | 3 | 24/08/2017 |
L11 | 2 | 25/08/2017 |
L15 | 4 | |
L15 | 5 | 24/08/2017 |
L14 | 5 | 24/08/2017 |
L1 | 2 | |
L21 | 2 | 01/08/2017 |
L30 | 1 | |
L12 | 1 | 24/08/2017 |
I have more than 20 files with two or tree format.
Thank you
Solved! Go to Solution.
Hi @Yasser92,
Yes you can combine these files. I think you will be using Append/ Merge Queries for the same as per your need.
One take is, when you combine these data sets, you can remove the unwanted columns and combine the data sets. In case if there is a file that has an additional column, that column also will get appended while all the records who don't have that information will be null
Hi @Yasser92,
Yes you can combine these files. I think you will be using Append/ Merge Queries for the same as per your need.
One take is, when you combine these data sets, you can remove the unwanted columns and combine the data sets. In case if there is a file that has an additional column, that column also will get appended while all the records who don't have that information will be null
Thank You lot @Thejeswar,
Do you think that Append Merge will be useful in my case even if I have more than 20 excel's data sheets
Hi,
Suppose you have 20 different files in your DB and you want them to make 1 data set, then the only option is UNION or UNION ALL
Similarly, as far as I know, appending the data sets in Power BI is the way to go as it is the equivalent of SQL Union in Power BI
Are you using the Combine Binaries (folder query)?
Thank you for the response.
Yes, I'm using a file as a source
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |