{"id":3184,"date":"2020-07-22T15:16:42","date_gmt":"2020-07-22T07:16:42","guid":{"rendered":"https:\/\/owa.sodexo-cn.com\/blog\/?p=3184"},"modified":"2020-07-22T15:19:07","modified_gmt":"2020-07-22T07:19:07","slug":"combine-multiple-excel-into-one-excel","status":"publish","type":"post","link":"https:\/\/owa.sodexo-cn.com\/blog\/?p=3184","title":{"rendered":"Combine multiple excel into one excel"},"content":{"rendered":"\n<p>The site will merge multiple excel format data into one excel every month. Example: Monthly data from January to December now need to be aggregated together for annual data. This is a very common requirement, how to solve it?<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Traget<\/strong><\/h4>\n\n\n\n<p>There is a folder containing several Excel documents, and each document has several worksheets. At the same time, the documents in this folder may be increased or decreased at any time. Therefore, we need to find a data that can be applied to a variable number of documents at any time and can capture specific areas in each document. <strong>Which method is the most effective? The answer is: Power Query<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"576\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/45-1024x576.jpg\" alt=\"\" class=\"wp-image-3163\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/45-1024x576.jpg 1024w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/45-300x169.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/45-768x432.jpg 768w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/45-525x295.jpg 525w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/45-480x270.jpg 480w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/45.jpg 1080w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"340\" height=\"422\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/46.jpg\" alt=\"\" class=\"wp-image-3164\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/46.jpg 340w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/46-242x300.jpg 242w\" sizes=\"auto, (max-width: 340px) 100vw, 340px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Steps<\/h4>\n\n\n\n<p>The specific operation is divided into three parts, which only takes about 2 minutes in total:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Create basic queries<\/li><li>Custom query content<\/li><li>Back to results<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>&gt;&gt;&gt;&nbsp;Step 1: Create a new query<\/strong><\/h4>\n\n\n\n<p>First we create a basic query that contains information about each workbook in the folder. The path can be: data-&gt;get data-&gt;from file-&gt;from folder<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"984\" height=\"623\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/47.jpg\" alt=\"\" class=\"wp-image-3165\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/47.jpg 984w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/47-300x190.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/47-768x486.jpg 768w\" sizes=\"auto, (max-width: 984px) 100vw, 984px\" \/><\/figure>\n\n\n\n<p>Select the folder path and click OK.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"699\" height=\"219\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/48.jpg\" alt=\"\" class=\"wp-image-3166\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/48.jpg 699w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/48-300x94.jpg 300w\" sizes=\"auto, (max-width: 699px) 100vw, 699px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"878\" height=\"398\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/49.jpg\" alt=\"\" class=\"wp-image-3167\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/49.jpg 878w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/49-300x136.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/49-768x348.jpg 768w\" sizes=\"auto, (max-width: 878px) 100vw, 878px\" \/><\/figure>\n\n\n\n<p>After loading the query results, click Edit to enter the Power Query editor.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"593\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/50.jpg\" alt=\"\" class=\"wp-image-3168\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/50.jpg 964w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/50-300x185.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/50-768x472.jpg 768w\" sizes=\"auto, (max-width: 964px) 100vw, 964px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>&gt;&gt;&gt;&nbsp;Step 2: Custom query<\/strong><\/h4>\n\n\n\n<p>Use the toolbar tool to add columns. The path is Add Column -&gt; Custom Column. Then enter the following command:=Excel.Workbook(File.Contents([Folder Path]&amp;[Name]))<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"593\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/51.jpg\" alt=\"\" class=\"wp-image-3169\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/51.jpg 964w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/51-300x185.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/51-768x472.jpg 768w\" sizes=\"auto, (max-width: 964px) 100vw, 964px\" \/><\/figure>\n\n\n\n<p>For our newly added column, we need to expand. Simply expand all of its columns.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"593\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/52.jpg\" alt=\"\" class=\"wp-image-3170\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/52.jpg 964w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/52-300x185.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/52-768x472.jpg 768w\" sizes=\"auto, (max-width: 964px) 100vw, 964px\" \/><\/figure>\n\n\n\n<p>At present, we have not got any data information, so we need to further expand. Expand the workbook.data column. Considering that the data information we need are in columns B and C of the original worksheet file. So here only need to expand Column2 and Column3.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"593\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/53.jpg\" alt=\"\" class=\"wp-image-3171\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/53.jpg 964w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/53-300x185.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/53-768x472.jpg 768w\" sizes=\"auto, (max-width: 964px) 100vw, 964px\" \/><\/figure>\n\n\n\n<p>After unfolding, we got a table with lots of useless information.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"593\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/54.jpg\" alt=\"\" class=\"wp-image-3172\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/54.jpg 964w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/54-300x185.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/54-768x472.jpg 768w\" sizes=\"auto, (max-width: 964px) 100vw, 964px\" \/><\/figure>\n\n\n\n<p>Filter the information of Column2 and only keep Total.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"593\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/55.jpg\" alt=\"\" class=\"wp-image-3173\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/55.jpg 964w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/55-300x185.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/55-768x472.jpg 768w\" sizes=\"auto, (max-width: 964px) 100vw, 964px\" \/><\/figure>\n\n\n\n<p>Delete useless columns, leaving only Name, Workbook Name, and Workbook.Data.Column2.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"593\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/56.jpg\" alt=\"\" class=\"wp-image-3174\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/56.jpg 964w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/56-300x185.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/56-768x472.jpg 768w\" sizes=\"auto, (max-width: 964px) 100vw, 964px\" \/><\/figure>\n\n\n\n<p>After loading the query results, you get such an effect.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"984\" height=\"623\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/57.jpg\" alt=\"\" class=\"wp-image-3175\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/57.jpg 984w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/57-300x190.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/57-768x486.jpg 768w\" sizes=\"auto, (max-width: 984px) 100vw, 984px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>&gt;&gt;&gt;&nbsp;Step 3:<\/strong> Data Update<\/h4>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"822\" height=\"585\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/58.jpg\" alt=\"\" class=\"wp-image-3176\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/58.jpg 822w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/58-300x214.jpg 300w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2020\/07\/58-768x547.jpg 768w\" sizes=\"auto, (max-width: 822px) 100vw, 822px\" \/><\/figure>\n\n\n\n<p>As mentioned earlier, the results created by the query can be updated at any time. For example, as shown in the figure above, we copy a file3, as long as a simple update, so the data can be directly captured. It really takes 2 minutes to solve the problem of merging multi-document data once and for all.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The site will merge multiple excel format data into one &#8230; <a title=\"Combine multiple excel into one excel\" class=\"read-more\" href=\"https:\/\/owa.sodexo-cn.com\/blog\/?p=3184\" aria-label=\"\u9605\u8bfb Combine multiple excel into one excel\">\u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3184","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"views":1863,"_links":{"self":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3184"}],"version-history":[{"count":2,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3184\/revisions"}],"predecessor-version":[{"id":3187,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3184\/revisions\/3187"}],"wp:attachment":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}