{"id":3926,"date":"2022-01-14T00:04:02","date_gmt":"2022-01-14T08:04:02","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3926"},"modified":"2022-01-14T00:04:04","modified_gmt":"2022-01-14T08:04:04","slug":"how-to-combine-data-from-multiple-files-in-a-folder","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3926","title":{"rendered":"How to Combine Data From Multiple Files in a Folder"},"content":{"rendered":"\n<p>We have two options to combine the files.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Combine and Transform<\/li><li>Combine and Load<\/li><\/ul>\n\n\n\n<p>In this article, we will see how to use \u2018Combine and Transform\u2019.  Have a look at the files.\u00a0<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/Combine%20And%20Transform%20Data%20Of%20Multiple%20Files%20Located%20In%20A%20Folder%20In%20Power%20BI.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>See below the structure of each file,&nbsp;Employee1&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image002.png\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;&nbsp;Employee2&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image003.png\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Employee3&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image004.png\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Follow the below steps to combine and transform the data.&nbsp;<strong>Step 1<\/strong>&nbsp;To import data, go to \u2018Home\u2019 tab click on \u2018Get data\u2019 select the \u2018Folder\u2019 source then click \u2018Connect\u2019.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image005.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Paste the path of the folder and click ok&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image006.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;After clicking on \u2018Ok\u2019 you will see what is located in this folder. You can see the meta data of the files like name, type, when it last modified etc.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image007.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Click on highlighted text i.e. \u2018Combine and Transform\u2019.&nbsp;<strong>Step 2<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image008.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Choose the first file to combine and click on \u2018Ok\u2019. Now Power query editor will be opened and you can see that all three tables are combined.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image009.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Here you can see that Power BI added one additional column that specifies from which file is the specific row in the table.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image010.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;This column is very important, and we can use it like every other column to slice data based on its values (In this case its OFC_LOC). But in this case, we already have a Country in OFC_LOC column, therefore we do not need it and we will remove it. By having the column selected, we click Remove Columns.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image011.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image012.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p><strong>Step 3<\/strong>&nbsp;Now go to file tab and click on \u2018Close &amp; Apply\u2019. You can perform multiple tasks in power query editor and apply changes.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image013.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Your changes will be saved and shown on Power BI desktop window.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image014.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p><strong>Step 4<\/strong>&nbsp;Now you can create a report from this table. Here I am using Pie chart and two columns i.e. \u2018Ofc_loc\u2019 and \u2018Salary\u2019.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image015.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<p>&nbsp;Second report is showing the total Salary of all countries. To do so I am using card visualization with Sum of salary.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/csharpcorner-mindcrackerinc.netdna-ssl.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/Images\/image016.jpg\" alt=\"Combine and transform data of multiple files located in a Folder in Power BI\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<p>\u00a0Try these above steps, you will be able to create a combine table by using power query. You can transform the data as per your need in power query editor. I will write a separate article on Power Query Editor so continue with me. Thanks for reading.<\/p>\n\n\n\n<p>Ref: https:\/\/www.c-sharpcorner.com\/article\/combine-and-transform-data-of-multiple-files-located-in-a-folder-in-power-bi\/<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We have two options to combine the files. Combine and Transform Combine and Load In this article, we will see how to use \u2018Combine and Transform\u2019. Have a look at the files.\u00a0 See below the structure of each file,&nbsp;Employee1&nbsp; &nbsp;&nbsp;Employee2&nbsp; &nbsp;Employee3&nbsp; &nbsp;Follow the below steps to combine and transform the data.&nbsp;Step 1&nbsp;To import data, go <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3926\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","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,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1050,1051,1052],"class_list":["post-3926","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-combine-and-transform","tag-combine-data-from-multiple-files-in-a-folder","tag-load-data-from-multiple-files-in-a-folder"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3926","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3926"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3926\/revisions"}],"predecessor-version":[{"id":3927,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3926\/revisions\/3927"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}