Thankyou for this video, this solution is good when our data is not big. What will be the solution for when I have 74 coulmns in one file and there are more than 10 files.
More than 10 files is not an issue. As for 74 columns, do you really need all of them, delete what you will not use, and instead of typing column headers like my first example, your could have the headers prepared in a separate query to append.
No, you only enter the headers once regardless of the number of files, 3, 20 or 400. Ideally this issue is tackled at source and we don't get files with a big variety of headers, but this video shows 3 methods really for different cases. Typically, the idea of removing the header row and appending a prepared one is great. The final M example is for more specific cases.
If they are in subfolders, Power Query will pick them up also. Just select the highest folder. If they are in different directories, then use separate folder queries and append them at the end.
I'm on the Beta Insider channel [Microsoft 365 MSO (Version 2305 Build 16.0.16421.20000)], and following along (after spending an inordinate amount of time recreating workbooks), when I used Combine, it appended all three files and provided no header leaving all the headers in the data area. This was a good lesson, but would have been MUCH better if sample files had been provided.
Thank you. I'm very sorry I didn't put enough effort into this free lesson and you had to knock some sample files quick. I would assume with PQ not recognising the headers, you may have the PQ setting for recognising headers and data types set to off.
I have 32 CSV file with diffrent headers . Like in one csv i have 80 headers and in otgers I have 100. How we can combine that using power query. Or any method using vba if we put the required header and vba will fetch those from multiple csv
I don't fully understand the question but both Power Query and VBA can be used. If you're using Power Query and only need specific columns, you can simply remove what you do not need. This is a one time action as Power Query is refreshable.
Sir, Im facing some issue here. Not related to the video. When trying to mail merge, MS word is fetching incorrect value from the excel file. In excel file, I have a cell with its value as "AB", but in Mail Merge- i get "0". How do I troubleshoot? Also in excel, I have used sumproduct to calculate rank, it works perfectly but after mail merge, I'm getting incorrect value displayed. For 1 in excel, I get something like 0.00009999 For 2, I get 1.239999999 For 3, I get 3.0000001211 Values given here are not exact but i made them up so that u can get a rough quick idea of my problem.
Ok, for Mail Merge, I don't understand the problem. Not sure how AB can be translated to 0. For the second question, Excel lacks precision for floating numbers. So you could add a ROUND function to remove decimal points before Word uses them with Mail Merge. Excel offers many rounding functions to fit your needs ROUNDUP, ROUNDDOWN, CEILING, FLOOR, MROUND etc.
I've spent 24hrs looking at this problem using numerous example videos - this one was simple, easy to follow and immediately fixed my issue - Thanks
You're very welcome! Thanks!!
Thank you,
Simple and clear
You're welcome!
This helped me this is very easy !!! No complicated like other videos thanks 👍👍
Glad it helped!
Thank you Thank you Thank you
Welcome!
What if we need to combine sheets instead of Files from folder ? will the process be same as for files ?
That was very helpful, thanks😀
No problem. Thank you.
Excellent! :-) Thank you!
Thank you too! It is my pleasure.
Thank you for sharing
My pleasure 👍
Thank you it helped me. :)
Awesome! Happy to help.
Thankyou for this video, this solution is good when our data is not big.
What will be the solution for when I have 74 coulmns in one file and there are more than 10 files.
More than 10 files is not an issue. As for 74 columns, do you really need all of them, delete what you will not use, and instead of typing column headers like my first example, your could have the headers prepared in a separate query to append.
this is easy with 3 headers, but how to do when we have 20+? should I keep typing all fields from all files?
No, you only enter the headers once regardless of the number of files, 3, 20 or 400.
Ideally this issue is tackled at source and we don't get files with a big variety of headers, but this video shows 3 methods really for different cases.
Typically, the idea of removing the header row and appending a prepared one is great. The final M example is for more specific cases.
What about if you have files that aren't in the same folder?
If they are in subfolders, Power Query will pick them up also. Just select the highest folder.
If they are in different directories, then use separate folder queries and append them at the end.
I'm on the Beta Insider channel [Microsoft 365 MSO (Version 2305 Build 16.0.16421.20000)], and following along (after spending an inordinate amount of time recreating workbooks), when I used Combine, it appended all three files and provided no header leaving all the headers in the data area.
This was a good lesson, but would have been MUCH better if sample files had been provided.
Thank you. I'm very sorry I didn't put enough effort into this free lesson and you had to knock some sample files quick.
I would assume with PQ not recognising the headers, you may have the PQ setting for recognising headers and data types set to off.
@@Computergaga THX for the lesson. Your voice is rather boring though.
I have 32 CSV file with diffrent headers . Like in one csv i have 80 headers and in otgers I have 100. How we can combine that using power query. Or any method using vba if we put the required header and vba will fetch those from multiple csv
Mapping table! Google it
I don't fully understand the question but both Power Query and VBA can be used. If you're using Power Query and only need specific columns, you can simply remove what you do not need. This is a one time action as Power Query is refreshable.
Sir, Im facing some issue here. Not related to the video.
When trying to mail merge, MS word is fetching incorrect value from the excel file.
In excel file, I have a cell with its value as "AB", but in Mail Merge- i get "0". How do I troubleshoot?
Also in excel, I have used sumproduct to calculate rank, it works perfectly but after mail merge, I'm getting incorrect value displayed.
For 1 in excel, I get something like 0.00009999
For 2, I get 1.239999999
For 3, I get 3.0000001211
Values given here are not exact but i made them up so that u can get a rough quick idea of my problem.
Ok, for Mail Merge, I don't understand the problem. Not sure how AB can be translated to 0.
For the second question, Excel lacks precision for floating numbers. So you could add a ROUND function to remove decimal points before Word uses them with Mail Merge. Excel offers many rounding functions to fit your needs ROUNDUP, ROUNDDOWN, CEILING, FLOOR, MROUND etc.
@@Computergaga sir did u saw the issue?