{"id":2324,"date":"2020-04-05T22:07:41","date_gmt":"2020-04-05T22:07:41","guid":{"rendered":"https:\/\/portfolio.accrualhub.com\/powergi2\/?p=139"},"modified":"2025-09-09T22:07:28","modified_gmt":"2025-09-09T22:07:28","slug":"power-query-files-from-folder-make-the-sample-file-the-one-with-the-most-columns","status":"publish","type":"post","link":"https:\/\/powergi.net\/es\/blog\/power-query-files-from-folder-make-the-sample-file-the-one-with-the-most-columns\/","title":{"rendered":"Power Query Files from Folder:"},"content":{"rendered":"\t\t
\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 \u201cSample File\u201d, 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 less columns than others?<\/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 the three files: Contact2, ContactType2 and Address Line 2.<\/em><\/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

From vision to execution<\/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

Whether you're just starting or scaling automation, we help turn your ideas into impactful solutions.<\/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

Enhance your business efficiency with our comprehensive Power Platform Consulting services<\/strong><\/a>. Our experts ensure seamless data integration across all files, leveraging Microsoft Power Platform\u2019s capabilities for robust automation and innovative solutions. Whether managing complex datasets or optimizing workflows, our team is dedicated to maximizing your organization\u2019s potential through tailored consulting services.<\/p>\n

\u00a0<\/p>\n\n

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

If you open the \u201cSample File\u201d query, and click the \u201cSource\u201d step settings, you will notice Power Query is using the first file as Sample (Option is selected at the moment of combining the files). This is an essential step in any Power Platform consulting service<\/strong><\/a> to ensure seamless data integration.<\/p>\n\n

\"\"<\/figure>\n\n
\n
\"\"\n
Option selected at the moment of combining the files<\/figcaption>\n<\/figure>\n<\/div>\n\n

If Power Query uses the workbook with less columns (in this example, Book3.xlsx) as sample file, it will exclude all the columns in Book2 not present in Book3, to avoid this, we should make sure the correct workbook is used as the Sample.<\/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

Automate tasks that slow you down<\/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

Free up your team\u2019s time and focus on strategic work with digital and robotic automation.<\/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
\"\"\n

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

Making the sample file the one with the highest number of columns<\/strong><\/h2>\n\n
    \n
  1. Duplicate the query that combines all the files<\/li>\n<\/ol>\n\n
    \n
    \"\"<\/figure>\n<\/div>\n\n
      \n
    1. Rename the query to \u201cColumn Count\u201d, then in this new query go to the step \u201cRenamed Columns<\/strong>\u201d, after the \u201cInvoke Custom Function1\u201d step, and click on \u201cDelete Until End\u201d.<\/li>\n<\/ol>\n\n
      \"\"<\/figure>\n\n
        \n
      1. Go to the last column in this new query and you will see a column that contains the table in each file. We need to know the number of columns each of the tables have.<\/li>\n<\/ol>\n\n
        \"\"<\/figure>\n\n
          \n
        1. Go to tab Add Column and click on Custom Function.\u00a0 Indicate the column name and write Table.ColumnCount([Transform File])<\/strong> \u2013 (you need to the reference the column that has the table records as shown above, make sure you check the name). Then click OK<\/strong>.<\/li>\n<\/ol>\n\n
          \"\"<\/figure>\n\n
          \n
          \"\"\n
          This should be the result<\/figcaption>\n<\/figure>\n<\/div>\n\n
            \n
          1. Go to \u201cSample file\u201d, in that query we\u2019re going to insert a step after \u201cSource\u201d and before \u201cNavigation\u201d, select the \u201cSource<\/strong> step\u201d and then click on \u201cMerge Queries\u201d.<\/li>\n<\/ol>\n\n
            \"\"<\/figure>\n\n
              \n
            1. Select the Column Count <\/strong>query in the second table dropdown and use the File Name<\/strong> as matching columns. Then hit OK<\/strong>.<\/li>\n<\/ol>\n\n
              \"\"<\/figure>\n\n
                \n
              1. Expand the new column, and select the \u201cCount\u201d field, click OK<\/li>\n<\/ol>\n\n
                \n
                \"\"<\/figure>\n<\/div>\n\n
                  \n
                1. Now that the column count per file is available in the \u201cSample File\u201d query just sort the column to have the file with the highest count first.<\/li>\n<\/ol>\n\n
                  \n
                  \"\"<\/figure>\n<\/div>\n\n

                  With this workaround you can be sure that Power Query will always use the correct file as sample, regardless of the name of the file.<\/p>\n\n

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

                  Table column count function: https:\/\/learn.microsoft.com\/en-us\/powerquery-m\/table-columncount<\/a><\/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>\n\t\t","protected":false},"excerpt":{"rendered":"

                  When we connect to a folder to combine all the files, Power Query creates a \u201cQuery\u201d called \u201cSample File\u201d, 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 less columns than others? In the […]<\/p>\n","protected":false},"author":2,"featured_media":2612,"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":[35,29,25],"tags":[13,24,16,19],"class_list":["post-2324","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-excel","category-power-bi-cat","category-power-query-cat","tag-automation","tag-data-transformation","tag-excel","tag-power-query"],"glossary_letter":null,"rank_math_title":null,"rank_math_description":"Advanced Power Query tips: Learn to combine folder files by selecting the sample with the most columns. Technical guide on the Power GI blog.","rank_math_focus_keyword":null,"_links":{"self":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/2324","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=2324"}],"version-history":[{"count":0,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/2324\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media\/2612"}],"wp:attachment":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media?parent=2324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/categories?post=2324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/tags?post=2324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}