Combine Data from Multiple Excel Files | Most Dynamic Method (Dynamic Columns & Sheets)

Поделиться
HTML-код
  • Опубликовано: 27 сен 2024

Комментарии • 101

  • @GoodlyChandeep
    @GoodlyChandeep  Год назад +5

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

  • @trustnoone6065
    @trustnoone6065 6 месяцев назад +3

    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

  • @masterjanglez
    @masterjanglez Год назад +2

    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 🙏

  • @damionc
    @damionc 9 месяцев назад +2

    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.

  • @rangs1966
    @rangs1966 6 дней назад

    Sir, Your Power query tutorials are excellent and always eagerly looking forward to new PQ tutorials. One clarification : Please clarify: You are using Table.Transform Columns and Excel.Workbook)(_) for converting binary into excel tables . In another situation you use Add Column and in the Dialog Box, type Excel.Workbook(). When do we have to use Table.TransformColumns and When do we have to use Add Column for converting Binary into table. Is there any difference . Looking forward to receiving your answer.

  • @MrDhunpagla
    @MrDhunpagla Год назад +2

    Magician of excel 🎉🎉

  • @MrJadison7
    @MrJadison7 Год назад +5

    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
      @MichaelVB226 11 месяцев назад +1

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

    • @sandroganci8817
      @sandroganci8817 4 месяца назад

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

    • @georgefox8458
      @georgefox8458 Месяц назад +1

      I think I’ve worked out how to do this so thought I’d share:
      You do need to use expand columns and then edit that step so the third argument is dynamic. It should look like this
      =Table.ExpandTableColumn(#”previousstepname”, “Data”, Table.ColumnNames(Table.Combine(#”previousstepname”[Data])))

    • @georgefox8458
      @georgefox8458 Месяц назад +1

      Use this in place of the step 4 in the video, good luck 😊

    • @MrJadison7
      @MrJadison7 Месяц назад +1

      Thank you @georgefox8458

  • @udayteja6595
    @udayteja6595 Год назад

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

  • @DavidWarbeck
    @DavidWarbeck 2 месяца назад

    This is great! Thank you. Is there any way to keep the File and Sheet names in the final data?

  • @IvanCortinas_ES
    @IvanCortinas_ES Год назад

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

  • @JG-wz6dq
    @JG-wz6dq 5 месяцев назад

    Woww this is a life saver! First on youtube

  • @dharmmu
    @dharmmu Год назад +1

    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".

  • @goodnewskasparyaodzramedo9097
    @goodnewskasparyaodzramedo9097 Год назад

    Good explanation, the logic so clear

  • @roywilson9580
    @roywilson9580 Год назад

    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.

  • @jimgleason4040
    @jimgleason4040 Год назад

    You Rock, Chandeep!

  • @mfachry3012
    @mfachry3012 Год назад

    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

  • @danielosorio217
    @danielosorio217 4 месяца назад

    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!

  • @DinoDelight
    @DinoDelight 5 месяцев назад

    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

  • @johnkiesel5106
    @johnkiesel5106 8 месяцев назад

    Clear explanation.

  • @kirux882
    @kirux882 3 месяца назад +1

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

  • @sowmyasrinivas6259
    @sowmyasrinivas6259 7 месяцев назад

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

  • @rogervandecraen9797
    @rogervandecraen9797 7 месяцев назад

    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

  • @DiariouDiallo-cm6bj
    @DiariouDiallo-cm6bj 2 месяца назад

    Great

  • @julianruizd.8489
    @julianruizd.8489 7 месяцев назад

    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?

  • @RameezMannil
    @RameezMannil Год назад +1

    How to retain the filename

  • @RogerStocker
    @RogerStocker 9 дней назад

    What to do if I don't just need the combined tables, bat also an information sitting in an other column?

  • @Chillman666
    @Chillman666 Год назад

    Very cool idea. Bravo 👏!

  • @afrojchuahan8133
    @afrojchuahan8133 8 месяцев назад

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

  • @Faisal63045
    @Faisal63045 Год назад

    Thank you for sharing

  • @Steggman
    @Steggman 8 месяцев назад

    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')

  • @bugsdenis547
    @bugsdenis547 6 месяцев назад

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

  • @davidmaldonadocastillejos3179
    @davidmaldonadocastillejos3179 Год назад

    Nice tip!

  • @cjimmer4877
    @cjimmer4877 Год назад

    Good stuff....thank you.

  • @abhishekawari919
    @abhishekawari919 Год назад

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

  • @dharmendrakumar-ge3tu
    @dharmendrakumar-ge3tu 10 месяцев назад

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

  • @kientran2467
    @kientran2467 2 месяца назад

    Is there any way to combine data in which the data from the file #2 is added below the data of file #1 and so on? Also after loading data to a table, I need to add a Comment column on the right side. Is there any way to keep my comments still aligned with their rows after Refresh? Thanks

  • @winbin82
    @winbin82 Год назад

    Wow.. amazing

  • @vaibhavgond
    @vaibhavgond Год назад

    Thanks!

  • @victor_wang_1
    @victor_wang_1 Год назад

    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.

  • @kuuuyajim
    @kuuuyajim 2 месяца назад +1

    How do you bring together the [Name] column when you drill down? I wanted to use this method instead of drilling down ng binary files since I need the column headers already promoted and dynamic. I understand that it's possible to add column on the Table record, but I can't quite figure out how. Any help would be appreciated or just a tip on how to do it. Thank you.

    • @georgefox8458
      @georgefox8458 Месяц назад

      Per my other comment- I think I’ve worked out how to do this so thought I’d share:
      You do need to use expand columns and then edit that step so the third argument is dynamic. It should look like this in place of step 4 in the video
      =Table.ExpandTableColumn(#”previousstepname”, “Data”, Table.ColumnNames(Table.Combine(#”previousstepname”[Data])))

    • @kuuuyajim
      @kuuuyajim Месяц назад +1

      @@georgefox8458 Thanks. I'll try to test that although I already found a different solution to this.

  • @shaffi31
    @shaffi31 3 месяца назад

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

  • @הובלותאחייםאבוסנינה

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

  • @manojjadhav4093
    @manojjadhav4093 6 месяцев назад

    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

  • @irfan78661
    @irfan78661 Год назад

    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

  • @RahulYadav-lp3jv
    @RahulYadav-lp3jv 4 месяца назад

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

  • @rajukhatri
    @rajukhatri Год назад

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

  • @vaibhavthakur_1997
    @vaibhavthakur_1997 Год назад +1

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

  • @patrykmurowski7296
    @patrykmurowski7296 Год назад

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

  • @vc9500
    @vc9500 11 месяцев назад

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

  • @comptecharles530
    @comptecharles530 5 месяцев назад

    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!

  • @manideepak6820
    @manideepak6820 Год назад

    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

  • @kanthu1931
    @kanthu1931 Год назад

    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
      @GoodlyChandeep  Год назад

      This video might help: ruclips.net/video/kwf-E3a3aOE/видео.htmlsi=9vJdjFr3WF0b-BaG

  • @shonwilson5219
    @shonwilson5219 Год назад

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

  • @bulbulahmed3098
    @bulbulahmed3098 Год назад

    ❤❤❤

  • @mohammadtawhidulislam4459
    @mohammadtawhidulislam4459 Год назад

    = 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
      @raitup00 Год назад

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

  • @markdhenderson1
    @markdhenderson1 9 часов назад

    I especially appreciate the promoteheaders step on 06:05

  • @sachinsingh3898
    @sachinsingh3898 7 месяцев назад

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

  • @surajD21
    @surajD21 11 месяцев назад

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

  • @AdminGRG
    @AdminGRG 7 месяцев назад

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

  • @felipealbieri_
    @felipealbieri_ 8 месяцев назад

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

  • @Dan_De_Man
    @Dan_De_Man 9 месяцев назад

    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
      @GoodlyChandeep  9 месяцев назад

      ruclips.net/video/0ZOY8is-bgY/видео.htmlsi=BDbKvEyw2sphXqaN

  • @vtanz
    @vtanz 5 месяцев назад

    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

  • @jackjobriencorso
    @jackjobriencorso 5 месяцев назад

    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!

  • @jessyjose-vt9er
    @jessyjose-vt9er 2 месяца назад

    Thank you so much . this helped me a lot bro .. well explained

  • @alexrosen8762
    @alexrosen8762 Год назад +1

    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 🙏

  • @lionels839
    @lionels839 Год назад

    GOATly

  • @jeremysmith6245
    @jeremysmith6245 Год назад

    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.

  • @sanumpzha
    @sanumpzha Год назад

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

  • @krisssh0097
    @krisssh0097 Год назад

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

  • @danieljoubert7937
    @danieljoubert7937 7 месяцев назад

    Super cool video, thanks Sir

  • @abhishekawari919
    @abhishekawari919 Год назад

    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
      @GoodlyChandeep  Год назад

      ruclips.net/video/0ZOY8is-bgY/видео.html

  • @bankimdas9517
    @bankimdas9517 Год назад

    Thank you sir for bringing this video

  • @deda118
    @deda118 Год назад

    Excellent pace. No frills and waste of time.

  • @mcwahaab
    @mcwahaab Год назад

    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
      @GoodlyChandeep  Год назад +1

      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
      @mcwahaab Год назад

      This is well noted and I will consider it. Thanks

  • @spen2431
    @spen2431 Год назад

    Amazing ! 💥💥💥💥

  • @rahulvishwakarma2119
    @rahulvishwakarma2119 Год назад

    👍

  • @josecarlosconejo5724
    @josecarlosconejo5724 Год назад

    Really good!!!

  • @Sheikh_Run_A
    @Sheikh_Run_A Год назад

    A perfect mentor ❤

  • @mikedbman
    @mikedbman 4 месяца назад

    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?