{"id":2325,"date":"2020-04-19T03:18:58","date_gmt":"2020-04-19T03:18:58","guid":{"rendered":"https:\/\/portfolio.accrualhub.com\/powergi2\/?p=178"},"modified":"2025-09-11T18:53:38","modified_gmt":"2025-09-11T18:53:38","slug":"combine-files-with-slightly-different-columns-advanced-solution","status":"publish","type":"post","link":"https:\/\/powergi.net\/de\/blog\/combine-files-with-slightly-different-columns-advanced-solution\/","title":{"rendered":"Combine Files 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

When we connect to a folder to combine all the files, Power Query creates a \u201cQuery\u201d called Sample File<\/em>, which is used as example to process each file, the results will depend on the columns that exist in this file. But what happens when any of the files have missing columns?<\/strong><\/p>\n\n

In the below example, 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.<\/em><\/p>\n\n

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

If Power Query uses the workbook with less columns as Sample file<\/em>, it will exclude all the columns not present in Book3.<\/p>\n\n

\"\"\n
Contact2, ContactType2 and Address Line 2 are now missing from the resulting table<\/figcaption>\n<\/figure>\n\n

We have shown in previous posts two different ways of working around this issue, by (1) modifying the Sample File (for unformatted tables & csv files<\/strong><\/a>) and by (2) Forcing power query to use as sample file the file with most columns<\/a>.
In this post, a different approach will be explained: Force Power Query to recognize All<\/strong> the columns you want<\/strong> to be in your final report.<\/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

Von der Vision zur Umsetzung<\/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

Ob Sie gerade erst mit der Automatisierung beginnen oder diese ausbauen, wir helfen Ihnen, Ihre Ideen in wirkungsvolle L\u00f6sungen zu verwandeln.<\/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

We\u2019ll achieve this by listing all the columns we need in a separate table, and then we\u2019ll use this list as a base for our resulting query instead of whatever columns are in the selected Sample file<\/em>.\u00a0<\/p>\n

This method is often used in Power Apps Beratungsdienste<\/strong><\/a> to ensure all relevant data points are retained when working with dynamic datasets.<\/p>\n

Unser Power Platform Beratungsleistungen<\/strong><\/a> streamline automation and data management, addressing potential issues with inconsistent file columns. By leveraging our expertise, we ensure that all necessary data points are captured effectively, offering a robust solution for businesses using Power Apps consulting services to handle dynamic datasets efficiently.<\/p>\n

\u00a0<\/p>\n\n

Listing the all the columns you need <\/strong><\/h2>\n\n

Before we begin, please make sure:<\/p>\n\n

(1) You\u2019re connected to the folder (Get Data -> from folder\u2026)<\/p>\n\n

(2) Your Transform Sample File<\/em> query recognizes your column names correctly and NOT as \u201cColumn1\u201d, \u201cColumnX\u201d, \u2026. (if your data is formatted an excel table, this isn\u2019t an issue, if you have unformatted data, check out this post<\/a>).<\/p>\n\n

\"\"\n
This is how your \u201cTransform Sample File\u201d query should look<\/figcaption>\n<\/figure>\n\n
    \n
  1. Identify all the columns you need in the query results. In this specific case, we need these:<\/li>\n<\/ol>\n\n