{"id":19077,"date":"2022-11-05T08:01:45","date_gmt":"2022-11-05T02:31:45","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/"},"modified":"2022-11-05T08:01:45","modified_gmt":"2022-11-05T02:31:45","slug":"solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/","title":{"rendered":"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Sorry for the length of this response, but there are a lot of steps involved and I included quite a bit of screen clips as well. I believe this solution does what you are looking for.<\/p>\n

I start with files in a folder: <\/p>\n

\"enter<\/a><\/p>\n

Feb-19.xlsx contains two tabs:<\/p>\n

\"1.<\/a>
\n\"2.<\/a><\/p>\n

Jan-19.xlsx contains three tabs:<\/p>\n

\"1.<\/a>
\n\"2.<\/a>
\n\"3.<\/a><\/p>\n

I open a new Excel file, then click Data > New Query > From File > From Folder and either type in, or use the Browse button to navigate to, the location of the folder that has the files. (When I navigate to my OneDrive folder, my path has SkyDrive.old in it, but it is my OneDrive folder that you saw in the first image above.) Then I click OK:<\/p>\n

\"enter<\/a><\/p>\n

Then I click Transform Data:<\/p>\n

\"enter<\/a><\/p>\n

This appears:<\/p>\n

\"enter<\/a><\/p>\n

I click Home > Manage Parameters (the words, with the drop-down arrow) > New Parameter, and I set it up like this and click OK. <\/p>\n

\"enter<\/a><\/p>\n

After clicking OK, this appears: <\/p>\n

\"enter<\/a><\/p>\n

You can see that I entered the path for the folder that has the files. I can change this parameter value later if I want to use a different folder path. <\/p>\n

To do that, I would click on \"enter<\/a> in the left pane. Clicking it would bring me to this same place, where I can edit the value.<\/p>\n

Now, I click on the query that I had already started. It is currently the only other item in the left pane. Clicking it brings this back up on the screen:<\/p>\n

\"enter<\/a><\/p>\n

I edit the text in the formula bar, replacing \"C:\\Users\\MARC_000\\SkyDrive.old\\Test\"<\/code> with FolderPath<\/code>. The result is the exact same table, but the formula bar has Folder.Files(FolderPath)<\/code>. Now, instead of using a hard-coded reference, the query is using the parameter value.<\/p>\n

Then, just because I want to, I change the query’s name to “Main Query.” You can do that by clicking on the query in the left pane, then changing the name in the PROPERTIES at the top of the right pane.<\/p>\n

Next, I select both the Content<\/em> and Name<\/em> columns, and then Home > Remove Columns (the words, with the drop-down arrow) > Remove Other Columns to get this:<\/p>\n

\"enter<\/a><\/p>\n

Then I click the \"enter<\/a> button to combine the files in the Content<\/em> column, which brings up this pop-up. Then I click on just the folder only, and then OK.<\/p>\n

\"enter<\/a><\/p>\n

Now there are more query entries in the left pane:<\/p>\n

\"enter<\/a><\/p>\n

I click on the new query, Transform Sample File from Test<\/em>, and see this:<\/p>\n

\"enter<\/a><\/p>\n

I select the Data<\/em> and Item<\/em> columns, and then Home > Remove Columns (the words, with the drop-down arrow) > Remove Other Columns to get this: <\/p>\n

\"enter<\/a><\/p>\n

—SEE EDIT AT BOTTOM OF ANSWER, WHICH REPLACES THE FOLLOWING—<\/strong><\/p>\n

Then I click the \"enter<\/a> button to expand the tables in the Data<\/em> column, which brings up this pop-up. Then I clear<\/em> the checkbox beside “Use original column name as prefix” and click OK.<\/p>\n

\"enter<\/a><\/p>\n

—RETURN FROM EDIT AT BOTTOM OF ANSWER TO CONTINUE—<\/strong><\/p>\n

Which yields this:<\/p>\n

\"enter<\/a><\/p>\n

Then I filter out null values from the Column1<\/em> column. (Click the down arrow at the top of the column and deselect null.)<\/p>\n

Then I click Add Column > Conditional Column, and set it up like this, and click OK:<\/p>\n

\"enter<\/a><\/p>\n

Which yields this:<\/p>\n

\"enter<\/a><\/p>\n

Then I select the new Custom<\/em> column and click Transform > Fill > Down, to get this:<\/p>\n

\"enter<\/a><\/p>\n

Then I filter out “Summary (Amount)” and “Summary (Units)” entries from the Column1<\/em> column. (Click the down arrow at the top of the column and deselect ‘Summary (Amount)’ and ‘Summary (Units)’.) Which yields this:<\/p>\n

\"enter<\/a><\/p>\n

Now I go back to the Main Query. In other words, click on Main Query in the left pane. There will be a “problem.” All I need to do is delete the last APPLIED STEP in the right pane: Changed Type. Once I delete that, all is good and I see this:<\/p>\n

\"enter<\/a><\/p>\n

But I also want file names, so I click on the APPLIED STEP that is before the one that is currently selected, “Expanded Transform File from Test” is selected, so I click “Removed Other Columns 1”, and in the formula bar, I change the code from Table.SelectColumns(#\"Filtered Hidden Files1\", {\"Transform File from Test\"})<\/code> to Table.SelectColumns(#\"Filtered Hidden Files1\", {\"Transform File from Test\", \"Name\"})<\/code>. This adds the Name<\/em> column and I see this:<\/p>\n

\"enter<\/a><\/p>\n

Then I go back to the last APPLIED STEP, which is “Expanded Transform File from Test” and now I see this:<\/p>\n

\"enter<\/a><\/p>\n

Then I click Transform > Use First Row as Headers and get this:<\/p>\n

\"enter<\/a><\/p>\n

Then I rename the DE<\/em> column to Sheet<\/em> and the Feb-19.xlsx<\/em> column to File Name<\/em>.<\/p>\n

Then I filter out “Custname” entries from the Custname<\/em> column. (Click the down arrow at the top of the column and deselect ‘Custname’.) Which yields this:<\/p>\n

\"enter<\/a><\/p>\n

Then I reordered the columns to get this:<\/p>\n

\"enter<\/a><\/p>\n

Then I select the Summary Type<\/em> column and click Transform > Group By, and fill out the pop-up box like this and click OK:<\/p>\n

\"enter<\/a><\/p>\n

Which yields this (these are your two tables):<\/p>\n

\"enter<\/a><\/p>\n

So then I right-click on the Main Query in the left pane, and select Reference. That gives me a new query named Main Query (2), with a table that looks just like the last image above. Now I click on the table in Summary (Units) row and get this:<\/p>\n

\"enter<\/a><\/p>\n

Then I repeat the process for the Summary (Amount): I right-click the Main Query in the left pane, select Reference, and then click on the table in the new query’s Summary (Amount) Row to get this:<\/p>\n

\"enter<\/a><\/p>\n

Lastly, I rename the two newest queries “Summary (Units)” and “Summary (Amount)”<\/p>\n

When you close and load, this will give you three new worksheets. One for each query. If you don’t want a worksheet for the Main Query (If you only want the Summary (Units) and Summary (Amount)) then, after you close and load and are back in Excel<\/em>, click Data > Show Queries. Then right-click the Main Query in the right pane and click Load To, then select “Only Create Connection” and click Load. Click Continue when you get the data loss warning.<\/p>\n

One more last thing: Do not<\/em> put the Excel Workbook that has this query in it in its source folder, with the files it is getting the information from. Keep it separate.<\/p>\n

—Edits to accommodate top rows having transactional information—<\/strong><\/p>\n

I’m adding the following to deal with sheets that might have rows of information above the Summary tables. Here’s what I came up with:<\/p>\n

In the answer above, beginning immediately after the step where I said: I select the Data<\/em> and Item<\/em> columns, and then Home > Remove Columns (the words, with the drop-down arrow) > Remove Other Columns to get this: <\/p>\n

\"enter<\/a><\/p>\n

I now add another column (Add Column > Custom Column), and I set it up like this:<\/p>\n

\"enter<\/a> <\/p>\n

This makes a duplicate of the Data<\/em> column, but adds an index within each of the nested tables, like this:<\/p>\n

\"enter<\/a><\/p>\n

Then I add another column to determine the index number associated with the start of each summary in each nested table:<\/p>\n

\"enter<\/a>
\n(You may want to search for “Summary (” or “Summary (Units)” instead of “Summary”<\/em>)<\/p>\n

Note that it is constructed similarly to the previous column, in that it is basically a duplicate of the Indexed<\/em> column, only with the Summary Index<\/em> column added within each nested table.<\/p>\n

\"enter<\/a><\/p>\n

Then I add another column like this, to determine the index position of the first Summary table’s first line for each nested table:<\/p>\n

\"enter<\/a><\/p>\n

and get this:<\/p>\n

\"enter<\/a><\/p>\n

Then I add one more column like this, to remove the top rows that I don’t want within each nested table:<\/p>\n

\"enter<\/a><\/p>\n

Which gives me this:<\/p>\n

\"enter<\/a>
\n(The table selected in this image is the one that had the extra information at the top. That information is gone now<\/em>.)<\/p>\n

Then I select the TopRowsRemoved<\/em> and Item<\/em> columns, and then Home > Remove Columns (the words, with the drop-down arrow) > Remove Other Columns to get this: <\/p>\n

\"enter<\/a><\/p>\n

Then, I click the \"enter<\/a> button to expand the tables in the TopRowsRemoved<\/em> column (instead of the Data<\/em> column, which we had done before), which brings up this pop-up that looks exactly the same as when we’d used the Data<\/em> column. Then I clear<\/em> the checkbox beside “Use original column name as prefix” and click OK.<\/p>\n

\"enter<\/a><\/p>\n

Then I delete the old Expanded Data step, under APPLIED STEPS in the right hand pane. If I don’t delete the Expanded Data step, I’ll get an error because it’s looking for the Data<\/em> column, which doesn’t exist. I didn’t use the Data<\/em> column this time. Instead, I used the TopRowsRemoved<\/em> column. <\/p>\n

At this point, the rest of my previous answer still applies, so refer back to where I wrote —RETURN FROM EDIT AT BOTTOM OF ANSWER TO CONTINUE— above.<\/p>\n

Here’s my M code for the “Main Query” query:<\/p>\n

let\nSource = Folder.Files(FolderPath),\n#\"Removed Other Columns\" = Table.SelectColumns(Source,{\"Content\", \"Name\"}),\n#\"Invoke Custom Function1\" = Table.AddColumn(#\"Removed Other Columns\", \"Transform File from Test\", each #\"Transform File from Test\"([Content])),\n#\"Filtered Hidden Files1\" = Table.SelectRows(#\"Invoke Custom Function1\", each [Attributes]?[Hidden]? <> true),\n#\"Removed Other Columns1\" = Table.SelectColumns(#\"Filtered Hidden Files1\", {\"Transform File from Test\", \"Name\"}),\n#\"Expanded Transform File from Test\" = Table.ExpandTableColumn(#\"Removed Other Columns1\", \"Transform File from Test\", {\"Column1\", \"Column2\", \"Column3\", \"Column4\", \"Column5\", \"Item\", \"Custom\"}, {\"Column1\", \"Column2\", \"Column3\", \"Column4\", \"Column5\", \"Item\", \"Custom\"}),\n#\"Promoted Headers\" = Table.PromoteHeaders(#\"Expanded Transform File from Test\", [PromoteAllScalars=true]),\n#\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"CustName\", type text}, {\"Opening\", type any}, {\"In\", type any}, {\"Out\", type any}, {\"Closing\", type any}, {\"DE\", type text}, {\"Summary (Units)\", type text}, {\"Feb-19.xlsx\", type text}}),\n#\"Renamed Columns\" = Table.RenameColumns(#\"Changed Type\",{{\"DE\", \"Sheet\"}, {\"Feb-19.xlsx\", \"File Name\"}, {\"Summary (Units)\", \"Summary Type\"}}),\n#\"Filtered Rows\" = Table.SelectRows(#\"Renamed Columns\", each ([CustName] <> \"CustName\")),\n#\"Reordered Columns\" = Table.ReorderColumns(#\"Filtered Rows\",{\"File Name\", \"Sheet\", \"CustName\", \"Opening\", \"In\", \"Out\", \"Closing\", \"Summary Type\"}),\n#\"Grouped Rows\" = Table.Group(#\"Reordered Columns\", {\"Summary Type\"}, {{\"AllData\", each _, type table}})\nin\n#\"Grouped Rows\"\n<\/code><\/pre>\n
Here’s my M code for the “Transform Sample File from Test” query, with the changes to accommodate top rows having transactional information:<\/p>\n
let\nSource = Excel.Workbook(#\"Sample File Parameter1\", null, true),\n#\"Removed Other Columns\" = Table.SelectColumns(Source,{\"Data\",\"Item\"}),\n#\"Added Index\" = Table.AddColumn(#\"Removed Other Columns\", \"Indexed\", each Table.AddIndexColumn([Data],\"Index\", 0, 1)),\n#\"Added Custom1\" = Table.AddColumn(#\"Added Index\", \"SummaryIndexed\", each Table.AddColumn([Indexed],\"Summary Index\", each try if Text.StartsWith([Column1],\"Summary\") then [Index] else null otherwise null)),\n#\"Added Custom2\" = Table.AddColumn(#\"Added Custom1\", \"IndexMins\", each List.Min([SummaryIndexed][Summary Index])),\n#\"Added Custom3\" = Table.AddColumn(#\"Added Custom2\", \"TopRowsRemoved\", each Table.RemoveFirstN([SummaryIndexed],[IndexMins])),\n#\"Removed Other Columns1\" = Table.SelectColumns(#\"Added Custom3\",{\"TopRowsRemoved\", \"Item\"}),\n#\"Expanded TopRowsRemoved\" = Table.ExpandTableColumn(#\"Removed Other Columns1\", \"TopRowsRemoved\", {\"Column1\", \"Column2\", \"Column3\", \"Column4\", \"Column5\", \"Index\", \"Summary Index\"}, {\"Column1\", \"Column2\", \"Column3\", \"Column4\", \"Column5\", \"Index\", \"Summary Index\"}),\n#\"Filtered Rows\" = Table.SelectRows(#\"Expanded TopRowsRemoved\", each ([Column1] <> null)),\n#\"Added Custom\" = Table.AddColumn(#\"Filtered Rows\", \"Custom\", each if Text.StartsWith([Column1],\"Summary\") then [Column1] else null),\n#\"Filled Down\" = Table.FillDown(#\"Added Custom\",{\"Custom\"}),\n#\"Filtered Rows1\" = Table.SelectRows(#\"Filled Down\", each ([Column1] <> \"Summary (Amount)\" and [Column1] <> \"Summary (Units)\"))\nin\n#\"Filtered Rows1\"\n<\/code><\/pre>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            7<\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] Sorry for the length of this response, but there are a lot of steps involved and I included quite a bit of screen clips as well. I believe this solution does what you are looking for. I start with files in a folder: Feb-19.xlsx contains two tabs: Jan-19.xlsx contains three tabs: I open a … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,3548,401],"class_list":["post-19077","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-powerquery","tag-vba"],"yoast_head":"\n[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Sorry for the length of this response, but there are a lot of steps involved and I included quite a bit of screen clips as well. I believe this solution does what you are looking for. I start with files in a folder: Feb-19.xlsx contains two tabs: Jan-19.xlsx contains three tabs: I open a ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-05T02:31:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/11\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets\",\"datePublished\":\"2022-11-05T02:31:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/\"},\"wordCount\":1499,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png\",\"keywords\":[\"excel\",\"powerquery\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/\",\"name\":\"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png\",\"datePublished\":\"2022-11-05T02:31:45+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/#primaryimage\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets - JassWeb","og_description":"[ad_1] Sorry for the length of this response, but there are a lot of steps involved and I included quite a bit of screen clips as well. I believe this solution does what you are looking for. I start with files in a folder: Feb-19.xlsx contains two tabs: Jan-19.xlsx contains three tabs: I open a ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/","og_site_name":"JassWeb","article_published_time":"2022-11-05T02:31:45+00:00","og_image":[{"url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/11\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png","type":"","width":"","height":""}],"author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets","datePublished":"2022-11-05T02:31:45+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/"},"wordCount":1499,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/11\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png","keywords":["excel","powerquery","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/","url":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/","name":"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"primaryImageOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/#primaryimage"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/11\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png","datePublished":"2022-11-05T02:31:45+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/#primaryimage","url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/11\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/11\/Solved-Excel-Power-Query-get-data-from-multiple-unfixed-files.png"},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-excel-power-query-get-data-from-multiple-unfixed-files-with-multiple-unfixed-sheets\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Excel Power Query: get data from multiple unfixed files with multiple unfixed sheets"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/19077","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=19077"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/19077\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=19077"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=19077"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=19077"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}