{"id":8026,"date":"2018-05-22T11:08:45","date_gmt":"2018-05-22T11:08:45","guid":{"rendered":"http:\/\/www.nurturetechacademy.in\/?p=8026"},"modified":"2018-05-22T11:08:45","modified_gmt":"2018-05-22T11:08:45","slug":"power-query-merge-multiple-workbooks-different-file-format","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/power-query-merge-multiple-workbooks-different-file-format\/","title":{"rendered":"Power Query- How to merge multiple workbooks with different file format"},"content":{"rendered":"

Hi All,<\/p>\n

Hope you all are doing great!<\/p>\n

As an Excel user, we maintain data in different files with different formats and at the end, we want to consolidate these files into excel.<\/p>\n

First, we need to understand the problem then we will discuss its solution in detail.<\/p>\n

Problems-<\/u><\/strong><\/p>\n

Lets suppose if we have a different file format data files e.g. \u00a0Excel (Xlsx), Notepad (. Txt) and Ms-Access (.accdb) and we want to compile these files in Excel, normally we copy and paste data in to excel file however due to different file format excel cannot understand the format as well as their styles, so after posting data in to excel, we arrange all the alignments, apply font, convert in to borders etc. etc. So basically, it is a time-consuming process.<\/p>\n

So, do we have any solution? Can this time-consuming process be reduced?<\/p>\n

Say \u201cYes\u201d<\/p>\n

Solution-<\/p>\n

This blog post will make us understand, not only how to upload all the files format but also How to create a link between desired folder and Excel master file. We can make this process very easy.<\/p>\n

We will understand the whole process step by step and you can download the practice data file by clicking on this link \u2013<\/p>\n

Step1: –\u00a0 In our earlier blog post, we have already understood the process of \u201cHow to merge multiple worksheets data into one\u201d, If you still have not read that you can click on this link merge-multiple-worksheets-data-excel-power-query<\/a>.\u00a0 As I said above, we have a different file’s format with name Data-1, Data-2, Data-3 into one folder. \u00a0You can see the reference image below-<\/p>\n

\"Pic-1\"<\/p>\n

We want to consolidate these files into one excel workbook.<\/p>\n

How do we do that?<\/p>\n

Step:2- Get the data in Power Query-<\/strong><\/p>\n

we will perform this process through \u201cMerge Query\u201d which is available in the Excel Add-ins \u201cPower Query\u201d.<\/p>\n

Power Query-<\/strong> \u00a0Power Query is an Excel add-in that can be used for data discovery, reshaping the data and combining data coming from different sources. Power Query is one of the Excel add-ins provided as part of Microsoft Power BI self-service solution.<\/p>\n

Merge Query-<\/strong>\u00a0 we can merge more than one data files into one in a systematic manner.<\/p>\n

Note: This option(Power Query) can be used by only Ms-office 2013 or upper version user like Ms-Office 2016 or Office 365.<\/em><\/p>\n

The user of Ms-office 2013 can download power query by clicking on this link-<\/em>Microsoft power query<\/a><\/p>\n

\"Pic-2\"<\/p>\n

\"Pic-3\"<\/p>\n

Select your system type -> right side download summary -> click on Next and your file downloading will start.<\/em><\/p>\n

Where can you find Power Query? –<\/em><\/p>\n

Ms-Office 2013- There will be an additional tab added to ribbon tab beside view tab.<\/em><\/p>\n

Ms-office 2016 or 365- No need to download power query for this user as it is already inbuilt.<\/em><\/p>\n

You can get it into Data tab.<\/em><\/p>\n

Problem- If power query does not appear in Excel ribbon tab-<\/p>\n

File-> Option-> Add ins-> Excel COM add ins-> Go-> click on Power query -> OK<\/em><\/p>\n

Get data-<\/p>\n

Open blank excel workbook-> Data tab->Get data->From File-> From Workbook-> Browse your file -> Select it -> Ok<\/p>\n

\"pic-4\"<\/p>\n

Select File->Import<\/p>\n

\"pic-5\"<\/p>\n

In Navigator-> select your table name->Click on Edit<\/p>\n

\"pic-6\"<\/p>\n

When you click on Edit, Data will open in \u201cPower Query\u201d window-> Here you can format your data-<\/p>\n

E.g. Apply Date format-> select date column-> Home tab-> Data type-> Date<\/em><\/p>\n

Apply Text format-> Select Product-> Home tab-> Data Type-> Text<\/em><\/p>\n

Apply Decimal number-> Select Units-> Home tab-> Data Type-> Decimal Number<\/em><\/p>\n

\"Pic-7\"<\/p>\n

When formatting is done, \u201cClick on Close & Load to \u201coption<\/p>\n

\"pic-8\"<\/p>\n

In this window, you must select \u201cOnly create connection\u201d option.<\/p>\n

\u201cOnly create connection\u201d- On this stage, we want to make a connection between sheet.<\/p>\n

\"pic-9\"<\/p>\n

Connection properties will show the right side of the window in Queries & Connection.<\/p>\n

\"pic-10\"<\/p>\n

Get Notepad file<\/strong>-> You can follow the step2 process.<\/p>\n

\"Import<\/p>\n

Get Ms-Access File-><\/strong> You can follow step:2 process<\/p>\n

\"Import<\/p>\n

So, all the data has been uploaded successfully.<\/p>\n

\"Pic-12\"<\/p>\n

Step3:- Apply Merge Query-<\/strong><\/p>\n

Go to data tab-> Get Data-> Combine queries<\/p>\n

\"Pic-14\"<\/p>\n

After clicking we will get a Merge window in which from the drop down (Highlighted in black color) select first two table e.g. Sales & Sales1.<\/p>\n

After selecting table matched any two headings in both tables (Highlighted in Red color), Once this process has been accomplished you will get a message below the screen (Highlighted in Green\u00a0color) then Ok.<\/p>\n

\"Pic-15\"<\/p>\n

Step4:- Add More query-<\/strong><\/p>\n

As we have added two tables only, however, we have another one, to add that go to Home tab-> Merge queries -><\/p>\n

\"Pic-16\"<\/p>\n

Click on the drop-down list and select another heading (Highlighted in Black Colour) and same matched two headings (Highlighted in Rec color)-> Ok.<\/p>\n

\"Pic-17\"<\/p>\n

Step5: Extract merged data from the table-<\/strong><\/p>\n

Right click on your table data-> Remove other columns<\/p>\n

\"Pic-18\"<\/p>\n

Expand the Table data by clicking on right side of Heading -> Ok<\/p>\n

\"pic-19\"<\/p>\n

Home tab-> Close & Load to<\/p>\n

\"Pic-20\"<\/p>\n

Select any one of the formats-<\/p>\n

1- Table- If you want to merge data in to table format<\/p>\n

2- Pivot table- Directly convert data in Pivot table and create a report<\/p>\n

3- Pivot chart- you can directly convert data into a chart<\/p>\n

4- Existing worksheet-\u00a0 Not only you can upload merge data on your current worksheet or any other existing worksheet.<\/p>\n

5- New worksheet- We can upload merge data in a new worksheet.<\/p>\n

\"Pic-21\"<\/p>\n

Holy! \u201c98371\u201d data has been uploaded instantly with the same format.<\/p>\n

\"Pic-22\"<\/p>\n

Happy Learning \ud83d\ude0a<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

Hi All,
\nHope you all are doing great!
\nAs an Excel user, we maintain data in different files with different formats and at the end, we want to consolidate these files into excel.
\nFirst, we need to understand the problem <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":8049,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[265,266,263,243,264,257,260,259,249,256,248,244,250,252,245,253,254,246,251,267,255,262,258,247,261],"class_list":["post-8026","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-corporate-training-companies","tag-corporate-training-companies-in-delhi","tag-corporate-training-courses","tag-corporate-training-in-delhi","tag-corporate-training-programs","tag-excel-course-online","tag-excel-courses-online","tag-excel-online-course","tag-excel-online-training","tag-excel-training-in-delhi","tag-excel-training-online","tag-learn-microsoft-excel","tag-learn-ms-excel-online","tag-learning-excel-online","tag-learning-microsoft-excel","tag-microsoft-excel-course","tag-microsoft-excel-online-training","tag-microsoft-excel-training","tag-ms-excel-learning","tag-ms-excel-online-training","tag-ms-excel-training-in-delhi","tag-online-corporate-training","tag-online-excel-course","tag-online-excel-training","tag-online-excel-training-courses"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8026","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=8026"}],"version-history":[{"count":1,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8026\/revisions"}],"predecessor-version":[{"id":8050,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8026\/revisions\/8050"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/8049"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=8026"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=8026"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=8026"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}