{"id":2323,"date":"2020-04-05T04:04:09","date_gmt":"2020-04-05T04:04:09","guid":{"rendered":"https:\/\/portfolio.accrualhub.com\/powergi2\/?p=113"},"modified":"2025-04-02T22:01:21","modified_gmt":"2025-04-02T22:01:21","slug":"power-query-tip-how-to-combine-unformatted-tables-with-slightly-different-columns","status":"publish","type":"post","link":"https:\/\/powergi.net\/es\/blog\/power-query-tip-how-to-combine-unformatted-tables-with-slightly-different-columns\/","title":{"rendered":"Learn How to Combine Tables With Slightly Different Columns"},"content":{"rendered":"
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\n

Excel\u2019s Power Query allows you to append several files from a folder using the \u201cGet Data-> From File \u2013 > From Folder\u201d option. One of the main requirements to achieve this is that all the files in the folder need to have the same format when it comes to columns, but what happens when some of your files have less columns than the main one?<\/strong><\/p>\n\n

Let\u2019s see how to solve this problem using the below example:<\/p>\n\n

\"\"\n
We have three files with 5 columns in common, but there are a couple that are not present in all the files: Contact2, ContactType2 and Address Line 2.<\/figcaption>\n<\/figure>\n\n

If the data inside your files is formatted as an Excel Table<\/strong>, this shouldn\u2019t be an issue, because Excel will automatically recognize the tables and headers and place them based on the column name and not on position:<\/p>\n\n

\"\"<\/figure>\n

\u00a0<\/p>

\t\t
\n\t\t\t
\n\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Automatiza las tareas que te ralentizan<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

Libere el tiempo de su equipo y conc\u00e9ntrese en el trabajo estrat\u00e9gico con la automatizaci\u00f3n digital y rob\u00f3tica.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n

However, if you\u2019re dealing with more complex data structures or integrating Power Query with other Microsoft tools, you might need additional expertise. That\u2019s where Microsoft Power Platform consulting services<\/strong><\/a> can be useful in optimizing data transformation processes.<\/p>\n\n

But, if your files are in csv<\/strong><\/a> format or if the data is not formatted <\/strong>as a table inside the file, this is how Power Query will place the data:<\/p>\n\n

\"\"\n
Columns data get mixed<\/figcaption>\n<\/figure>\n\n

The files were processed based on position and now we have Contact Type 2, Address Line 2 and Comments under the same column.<\/p>\n\n

The first thought would be to format the data in each of the files as tables, but what if you process 20-50 csv files a day? Nobody wants to manually manipulate 20 different files, let\u2019s better have Power Query do the trick for us!<\/p>\n\n

The solution: Promote Headers in the \u201cTransform Sample File\u201d <\/strong><\/h2>\n\n

After connecting to a folder, in the queries section, you will see a query called \u201cTransform Sample File\u201d, open it and you will notice that the column names from our files are being set as the first row, and not as actual headers. Let\u2019s set this row as our header.<\/p>\n\n

\"\"<\/figure>\n\n

We do this by using the \u201cUse First Row as Headers\u201d option in the ribbon, after clicking it, you should see the correct column names but there will also be a new step called \u201cChanged Type\u201d, \u00a0make sure to delete this step from this sample query<\/strong>. (Note: this needs to be removed because this step is applying format to each column based on their name, for example, if the sample file has a \u201cContact2\u201d column, it will always try to format it in ALL your files, and if your file doesn\u2019t have it, it will error out).\u00a0<\/p>\n\n

This is how the final sample query should look:\u00a0\u00a0<\/p>\n\n

\"\"<\/figure>\n\n

Now, let\u2019s go back to the main query that is combining all the files. You will see an \u201cExpression error\u201d showing up instead of the results, this is because this query is referencing the old column names (Column1, Column2, Column3\u2026), to fix this error, just delete the \u201cChanged Type\u201d step.<\/p>\n\n

\"\"<\/figure>\n\n

After that, you will see how the headers are correctly placed and it\u2019ll show \u201cnull\u201d when the column is missing from a source file.\u00a0<\/p>\n\n

\"\"<\/figure>\n\n

One caveat to this is that the \u201cSample file\u201d should be a file containing the highest number of columns.
This can also be automated but requires an additional step, we have created an entry on the topic:

https:\/\/powergi.net\/blog\/power-query-files-from-folder-make-the-sample-file-the-one-with-the-most-columns\/<\/a><\/p>\n\n

\u00a0<\/p>

\t\t
\n\t\t\t
\n\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

\u00bfEst\u00e1 su negocio preparado para la automatizaci\u00f3n?<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

Automatiza procesos con Microsoft Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n\n

Beyond promoting headers<\/strong><\/h2>\n\n

Another scenario where you may want to modify the \u201cTransform sample file\u201d query is when files have some rows on top of the table as a \u201cDocument Header\u201d, if your data is not formatted as an excel table, same issue will happen.<\/p>\n\n

\"\"\n
Power Query will bring rows we don\u2019t need, and place our columns based on position, which we don\u2019t want.<\/figcaption>\n<\/figure>\n\n
\"\"<\/figure>\n\n

The underlying solution for this scenario is the same, you need to modify the \u201cTransform Sample File\u201d query, but adding a couple more basic steps (depending on your specific files). In this specific case, we can remove \u201cnull\u201d values in Column4 and that will make all unnecessary records go away.<\/p>\n\n

\"\"<\/figure>\n\n

After removing nulls, promote headers and don\u2019t forget to remove the \u201cChange type\u201d step (Like we did in previous example).

After that, go back to the main query and remove the existing \u201cChange Type\u201d step that is referring to the old column order.<\/p>\n\n

\"\"<\/figure>\n\n

Now, you\u2019re all set, unnecessary records have been removed and the headers are correctly assigned.<\/p>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>","protected":false},"excerpt":{"rendered":"

Combining files with Power Query is great but what happens when some unformatted files have less columns?<\/p>","protected":false},"author":2,"featured_media":2619,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"glossary_letter":"","footnotes":""},"categories":[25],"tags":[13,24,16,19],"class_list":["post-2323","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-power-query-cat","tag-automation","tag-data-transformation","tag-excel","tag-power-query"],"glossary_letter":null,"rank_math_title":null,"rank_math_description":null,"rank_math_focus_keyword":null,"_links":{"self":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/2323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/comments?post=2323"}],"version-history":[{"count":0,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/2323\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media\/2619"}],"wp:attachment":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media?parent=2323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/categories?post=2323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/tags?post=2323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}