Download File ⬇ - goodly.co.in/combine-data-from-multiple-excel-files

I'm a data analyst. Your videos are helping me a lot in real time projects. Thanks is a very small word. Love u bro

Powerful, clear, direct, controlling all scenarios => Goodly

Excellent pace. No frills and waste of time.

Another interesting video. On the caveat - a very good reason to always familiarise yourself with the data before even starting the ETL and reporting process. I would also try to ascertain ahead of time if the content type or layout of the data is likely to change in the future and provide plenty of rem statements against anything being added or just as bad taken away add to that some kind of documentation to explain how the visuals were produced and how to edit them to include extra data or remove fields etc. that have been deprecated.

This is really a comprehensive video......

I love your solution. Combined with your Remove junk rows video you would be able to do some transformations before promoting the headers if they were not in the first row. Great stuff.

Very cool idea. Bravo 👏!

Thanks, I am trying to make all my PQs unbreakable and your videos are amazing, easy to follow and live your explanation...making it simple

A perfect mentor ❤

Woww this is a life saver! First on youtube

Good explanation, the logic so clear

Clear explanation.

Super cool video, thanks Sir

I have been scouring the various information sources to find a possible solution to my problem. This is exactly what I needed. Thank you thank you 🙏

@GoodlyChandeep

10 ай бұрын

Glad it helped :)

Good stuff....thank you.

Thank you for sharing

Thank you sir for bringing this video

There have been numerous tutorials on this topic but not at the extent to the valuable hacks you have provided in this session. Thanks a lot 🙏

@GoodlyChandeep

10 ай бұрын

Thanks Alex :)

Really good!!!

Thanks!

Nice tip!

You Rock, Chandeep!

Amazing ! 💥💥💥💥

Wow.. amazing

Magician of excel 🎉🎉

@GoodlyChandeep

10 ай бұрын

Thanks 😉

While watching the video, I remembered that I had seen this video somewhere else and recalled where it was. Anyway, nice tutorial, Chandeep! 👍

Very detailed and concise explanations! Thank you! Question: I noticed that when expanding the tables their corresponding sheet (Step 3) shows the sheet name (under the Name.1 field but underneath each sheet also the following sheet name appears _xlnm_FilterDatabase which is not a sheet in the Excel file. Under the 'Kind' column it reads 'DefinedName' with the same .xlsx extension and it is hidden. What is this other row about? Also, each Excel sheet has a drop-down menu option for some cells. The menu options is located in another non-data sheet. But, these sheets also show as a sheet name when expanding the tables which reads 'Validation List.' How do I avoid getting data from the validation sheets? I appreciate your help!

Nice input, whatif we have csv format data in that case how to promote header in default?

Your videos are awesome... is there any way you can list the keystrokes you do as you do them, like the important ones, but also the others, every once in a while for those jumping in a bit later to the excel party. Thanks for making great content!

Thanks for sharing your knowledge. Could you post a video for this scenario extract text multiple text files into excel, remove first 2 top rows and then split into columns and final step merge all the files to create pivot chart. Thanks

Great tutorial! I am wondering... would you recommend to append a live table/tracker. I have one file per year but current year is in a table where my colleague inputs data daily. Would it be ok to combine this table with the rest?

great tutotial and an interesting pattern. Can this be applied in the folowing case: an excel file with 12 tabs each respresenting a month and each tab contains 9 tables ordered in rows table 1 is from row 10 to 50 and column A to K ; table 2 is from row 60 to 100 and column A to K and so on to table 9 then tab feb with the same structure. how to tackle this problem , thanks in advance Roger

Great Video!! I have a special issue with my data...my boss has requested I calculate the dwell time a material has spent in a location. I am having trouble calculating that. So if a material goes from the warehouse rack to a production line how do I calculate that in DAX? I have a confirmation and creation date for the pallet going both ways. I have missing confirmation dates for pallets that have not been confirmed to the destination location and it is causing errors in my calculation using datediff. Please help if you can and let me know if u require some sample data.

Thankyou soo much. How can i skip columns in only selected sheets using M query

@ 4:29 what do I use for csv files? If I do excel.workbook it lists all the csv files I have in each table, instead of giving me the data in the csv files?

Thanks for the good info...can you please make a video on the problem where we have to combine multiple files or sheet having inconsistant header to map with the updated header names and then combine.

@GoodlyChandeep

10 ай бұрын

kzread.info/dash/bejne/Yo6Du5qio5DIl7w.html

It is good way to include all the sheets but I think, it will be always good to know what sheet you are including and not hardcode the formula. The important point was to include the data header as LIST so that it can accommodate any addition or change of the header, which is most important while combining any table from different sheet. I always use this. Can you make a video,to combine data which is in one sheet, have different data sets (not in excel table format) with some common headers and only the differentiator is at the end of each data set there is a row which start with word "Total".

hi, i have one question,i had created multiple pages in Power view in tabular format and i need to get export all pages and to be in workbook format as like Excel work book,please suggest

What a greaat video man, but how do i activate the formula suggestion in power query excel, i use excel 2016 and also how about the origin file is not excel workbook, but it's CSV file ? Because the formula isn't work with csv file

hi how to updating power query when i used the I cloud ondrive or 3650 ??? thanks

How to tackle promote header part if CSV file are the source data?

Is there to keep the name of the excel files? Because after drilling down the name column disappears. Thank you for the awesome video.

@MichaelVB226

8 ай бұрын

I was trying to keep the file names also, but was not able to figure out how to accomplish this.

@sandroganci8817

Ай бұрын

i have the same issue, has anyone found a solution?

I love your videos ❤ I have a. Excel sheet with dynamic columns is there a query that extracts only the column names i mention irrespective of new columns that get added.😊

@GoodlyChandeep

10 ай бұрын

This video might help: kzread.info/dash/bejne/nauaj6dskZbHf6g.htmlsi=9vJdjFr3WF0b-BaG

Insightful as always! Just to ask, in your courses, do you provide customized solutions to work challenges? or if there is a forum to post issues

@GoodlyChandeep

10 ай бұрын

Absolutely! My goal is to empower students to solve problems independently. I'll explain you through each Power Query concept. And yes, there's a comment section for any questions you have!

@mcwahaab

10 ай бұрын

This is well noted and I will consider it. Thanks

Would this be live or dynamic? Example; At work we have several workbooks for different departments. I want to pull or push this data to a single master workbook. THEN if someone adds a new row to the department workbook, will that update to the master too? (i don't mind if i have to refresh rather than being 'live')

One challenge could be to add the filename and sheet name in the combined result

❤❤❤

Please tell how to convert binary into table if file format is cvs

How can I modify this query to get the filename and sheetname column?

Hi Sir Hope you doing Good!! This New update On Power Bi on 24th April has remove the Copy Data table option or it's not working in the latest version Due to what ch unable to copy the Data Table in excel Can you please help on small video ant the same pls.. I beleive no one has done that after updation

What if I need only first sheet of workbook irrespective of name of sheet, please guide

How to append multiple excel formats like xls,xlsx csv and tab delimited files together

Good video. In the end though, all it takes is 1 other sheet that should NOT be combined to break this. After all, how could you know which sheets to combine and which sheets not to combine? What works for me: After cleaning, load all the data into an MS Access database. For go-forward, query only the latest workbook and set up an error message if the sheet name or expected columns have changed and then just change that manually. Then append that cleaned data into the Access db. Then you can query all your data with no transformations needed. I find it interesting that no content creators talk about MS Access as it's a much more scalable option than combining workbooks which can easily error.

@GoodlyChandeep

10 ай бұрын

I agree!

How to retain the filename

👍

How to also add file name in the table in this steps

What if the headers are in caps in one sheet and lower case in another sheet

GOATly

How can I know which line is from which file if I don't have a date column?

Sir ,when running power query "intilazation of data sources error contact your administrator" is shown could you please provide a solution for this 😢

When i am combining data from multiple excel workbooks which extracted from zip format It consolidating only first 8 line items Can you please help me sir

What to do if column header is arrange not in sequence, they are randomly. In different workbook while combine.

@GoodlyChandeep

10 ай бұрын

The order doesn't matter

@vaibhavthakur_1997

10 ай бұрын

@@GoodlyChandeep ok thanks

What's this you are doing all days?? I don't know anything but I want to learn it. How can I learn it.

How can I get specific sheets from a single Excel files ?

Thank you so much for all the content you provide! If only you could spare us those fashion marks on your clothes, that would be just perfect!

thank you but how to do this with CSV files? it doesn't work when i put the (_,)

I edited the 2006 file and changed customer header to client and when I refreshed the query it just created a new column at the end called client. How do I stop it creating new column and just put the client column details into the customer column

@GoodlyChandeep

6 ай бұрын

kzread.info/dash/bejne/Yo6Du5qio5DIl7w.htmlsi=BDbKvEyw2sphXqaN

Try it but it limited to 3.6k+ lines. It can’t fully combine all data 😢 How to solve it?

= Table.TransformColumnNames(#"Filtered Rows1", {"Content", each Excel.Workbook(_)}) Then I am getting an error below- Expression.Error: We cannot convert a value of type List to type Function. Details: Value=[List] Type=[Type] Any help, please.

@raitup00

10 ай бұрын

Why are you using Table.TransformColumnNames() instead of Table.TransformColumns()?

I have an issue. When I do the Table.ExpandTableColumn (approx at 5:30 in the video) I get a "MASSIVE" Error that starts with this: Error Message:Object reference not set to an instance of an object. Stack Trace: at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message) at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel) at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing) at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_TableSource() at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource() at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled) Any suggestions?