• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Combine Files With Different Column Names in Power Query

You are here: Home / Power Query / Combine Files With Different Column Names in Power Query
combine files with different column names in power query
March 3, 2022 by Philip Treacy

I'm going to look at situations that appear to be fairly common: loading data into Power Query where the source data has columns with different names.

For example, in the following file received in August 2021, I have data that relates to various items over a 12 month period.

monthly source data

What the data is showing is values for the items for the month the file was created (Aug 21), the following 6 months (Sep 21 to Feb 22) and the preceding 5 months (Mar 21 to Jul 21)

Watch the Video

Subscribe YouTube

Download Power BI File

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download Sample PBIX File

Source Data CSV Files


This Works In Power BI and in Excel

The issues shown here and the solutions can be used in Power Query in both Power BI and Excel. I am using Power BI in my example.

Each month I get a new file showing values for that month, the next 6 months and the preceding 5.

So for Sep 21 the file looks like this

sample monthly data

It contains data for the months Apr 21 to Mar 22. It still contains 12 months of data, but it's moved along by 1 month compared to the previous month's file.

Clearly each file has the same number of columns and whilst some of those columns have the same names, the order of those columns changes every month.

If I load these files into PQ and combine them let's see what I get.

In Power BI Desktop, I load data from a folder: Get data -> More -> Folder

get data from folder

After browsing to the folder containing the CSV files, and selecting it, I choose to Combine & Transform the files.

combine and transform

In the data preview I accept the defaults and click OK.

preview combine files

The result of combining the files is this

combined data

It's not exactly what I want. For a start the columns contain the Month-Year names and these aren't consistent down the columns because for each new file, the months shift along by one.

What I want is a column for the item and a column for the months (dates) and a column for the numeric values. How to achieve this?

The first thing I'll do is duplicate this query so I can play around with it a little and investigate how to solve this problem.

duplicate query

With this done, I'm going to back up a bit and delete the last 2 steps

delete steps

This leaves me at the point in the query where I have the 3 CSV files as Tables in one of my columns.

steps deleted

Clicking on the first table (Aug 21) shows the data in that file

data from csv

Promoting the first row to column headers is the thing to do here

use first row as headers

Which does this

first row promoted to headers

To get all the months/dates into a column and all the values into another column, I can select the Item column and then Unpivot Other Columns

unpivot other columns

This gives me the data from this file in a format that I can use so I need to apply these transformations to all the files.

Back in my main query where I left it with just the files as tables in a column

steps deleted again

Add a Custom Column

add custom column

Using the Table.PromoteHeaders function I can manipulate each of the tables inside the Transform File column using this code

promote headers in tables

Clicking in the cell beside the August-21 table shows me that the first row has been promoted to headers, and it is the same for all 3 tables.

promoted headers

Next I need to unpivot the dates/values. Add another Custom Column and enter this code

unpivot tables

Note that I am referring to the Custom column as the location for the tables, and that I am naming the first column created by the unpivot Date

I get another column containing tables with the dates and values unpivoted into their own columns

unpivoted tables in column

Almost done. Next delete the first 3 columns leaving me with just the Custom.1 column.

I can expand the tables in this column now. Uncheck Use original column name as prefix

expand table columns

Giving me this

expanded data

There's one more thing to do. Remember that each month I get data for another month, so I'm going to end up with a lot of duplicate values for months that are contained in several files.

To see this, first change the Date column to the Date type

change to date type

Next sort the Item column in ascending order, then sort the Date column in ascending order.

It's plain to see now that there are duplicate values

duplicate values

To fix this, click on the Item column then click on the Date column, then right click on either column and choose Remove Duplicates.

remove duplicates

duplicates removed

Different Column Names - Same Data : 4 solutions

In this scenario I'm receiving data about clients and the values represent the amount I'm charging for some work done, on the dates shown.

In the first CSV file, for January, you can see there are 3 columns, the first one named Client.

jan csv data

In the February file there are also 3 columns but the first is named Customer.

feb csv data

If I load these files with Power Query (using the same process as in the first example) I get this.

csv loaded different column names

I get nulls in the first column because in the Feb CSV file that column is called Customer, and in Jan it's called Client. The Feb file has been loaded first so the first column takes its name from that file. As the Jan file does not have a Customer column, Power Query enters nulls here, and doesn't know what to do with the data in Jan's Client column so omits that entirely.

I have 4 solutions to this problem for you.

Solution 1

Let's back up a bit and get rid of these steps.

delete steps

I want to be left with the source CSV files as tables in a column.

csv tables in columns

I want to demote the column headers to become the first row in the tables. To achieve this, add a Custom Column with this code using the Table.DemoteHeaders function.

The table(s) being acted upon are in the Transform File (2) column.

demote headers

headers demoted

With the headers demoted in these tables I can skip the first row in each using the Table.Skip function. Add a Custom Column with this code

skip rows in table

first rows removed

With the first rows removed each table is left with just the data. I can now delete the first 3 columns from the query and expand the Custom.1 column

expand tables

expanded tables

All that is left is to rename the columns, set column data types and (if you wish) sort by date.

final sorted data

Solution 2

Duplicate the query from Solution 1

duplicate solution one query

Delete all the steps after Added Custom which demotes the table headers.

delete these steps

steps deleted

Delete the first 2 columns from the query and expand the tables in the Custom column.

delete columns and expand

Filter column 3 to remove the text "Date"

filter text date

Now you can rename the columns, set data types and sort as desired.

Solution 3

Duplicate the query from Solution 1, delete all the steps after Added Custom and delete the first 2 columns in the query to leave a single column Custom.

single column

Expand the tables in the column, then convert the Date column (Column 3) to the Date type.

convert date column to date type

This will result in errors in that column, where the row contained text.

errors from data type conversion

Right click on the Column 3 header and remove the errors

remove errors from column

errors removed

Rename the columns, set data types and sort as desired.

Solution 4

As before, duplicate the first query and delete the steps up to Added Custom so you are left with 3 columns in the query after the headers have been demoted.

solution four

The Feb file has the first column named Customer and this is what I want the column to be called in the final table.

So, delete the first 2 columns from the query and expand the tables in the Custom column

expanded tables

Promote headers : Transform tab -> Use First Row as Headers

use first row as headers

I can now filter out the header row from the table by filtering any of the columns to remove the appropriate text value. In this case I'll filter the Date column to remove the text "Date"

filter out text

final table

Rename the columns, set data types and sort.

Conclusion

Hopefully this will help you deal with situations where you also face importing columns with names that don't match up.

Let me know if you have any other scenarios with different column names causing you issues.

combine files with different column names in power query

More Power BI Posts

try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
fuzzy matching in power query

Fuzzy Matching in Power Query

Use fuzzy matching to compare non-identical text strings and match them together based on how similar one string is to the other.
handling http errors in power query and power bi

Handling HTTP Errors in Power Query and Power BI

Clearly communicate issues with custom messages when dealing with web scraping or API server errors. Download sample Excel and Power BI files
extract characters from strings in power query using text select and text extract

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

Learn a cool technique to extract or remove letters, numbers and special characters from strings. Sample workbook to download
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
sort by column in power bi

Sort-By Columns in Power BI

Create a Sort-By column to allow custom sort order in your Power BI Visuals. Download an example Power BI Desktop file
custom formatting strings in power bi

Custom Formatting Strings in Power BI

Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power BI
Previous Post:Power Automate Emails From ExcelAutomatically Email Reminders from Excel with Power Automate
Next Post:Top 10 Intermediate Excel Functions

Reader Interactions

Comments

  1. Moise Garrett

    March 4, 2022 at 6:13 am

    thank you so much

    Mo

    Reply
    • Philip Treacy

      March 4, 2022 at 10:00 am

      no worries.

      Reply
  2. Andrew

    March 3, 2022 at 11:28 pm

    Hi,

    Good stuff as always. Still getting my head around PQ.

    A scenario I’m facing often is what I now understand to be called “stacked data”.

    I can unstack it using normal Excel, but there is a lot of deleting, moving, duplicating columns etc. so that my end result is in a tabular format.

    Although my data is in a ‘csv’ format. I can have several lines that would be headers, quite often there is a “totals” row. 1 stack can contain 5 transactions, another 2, another 10.

    Oh and at the very end of the csv output is a grand total.

    Reply
    • Philip Treacy

      March 4, 2022 at 10:04 am

      Hi Andrew,

      Sounds like you are dealing with data that is already in a partial report format – such a sin! As you’re finding, the data just needs to be in a flat tabular format so that any reporting can be done later in Excel or Power BI.

      These posts may help you out in dealing with badly formatted source files

      https://www.myonlinetraininghub.com/power-query-unpivot

      https://www.myonlinetraininghub.com/power-query-unpivot-scenarios

      regards

      Phil

      Reply
  3. Higrm

    March 3, 2022 at 10:34 pm

    In your first example, where you have rolling months, you took the easy step and just deleted duplicates for months where you had multiple entries. This was fine in your made up example, because the numbers didn’t change when they went from being forecasts to actuals. In the real world, this isn’t what happens. You would need to create a rule as to how you want to handle these different values.

    Reply
    • Philip Treacy

      March 4, 2022 at 10:08 am

      Hi Higrm,

      yes the scenario you describe is feasible. The example I used is actually from a client where the only new data they got was new month’s data added each month.

      As you say, in your scenario it would be more complicated to weed out duplicates. You’d probably need to base this on keeping the most recent data so you’d be looking at manipulating the date column.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.