Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Load in Excel Files with Changing Column Position and Names in Power Query Editor

I have an Excel file that looks similar to this table.

Screen Shot 2023-08-25 at 12.46.18 PM.png

Though sometimes, the 4th and 5th columns get switched (including their values) and are renamed a little differently within Excel to look something like this: 


Screen Shot 2023-08-25 at 12.46.57 PM.png


How can I format it in Power Query Editor so that it would be able to load in both files with no issues?

Super User
Super User


The next time you post a question, please share data in a format that can be pasted in an MS Excel file.  Sharing a picture just does not help at all.  Try these steps:

  1. In a folder, store 2 Excel files - one with each table shown in your first post
  2. Load another table (called Replacements) in PowerBI.  This should be a 2 columns table like this


Ensure that the entries in the first column or the Replacements table are spelled the same way (including upper/lower case) as those in yoru source tables .  Now paste this code in the Query Editor

    Source = Folder.Files("C:\Users\mathu\Desktop\Data"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content], true)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Data"}),
    #"Standardised headings" = Table.TransformColumns(#"Removed Other Columns1", {{"Data", each Table.RenameColumns(_,Table.ToRows(Replacements),MissingField.Ignore)}}),
    Custom1 = Table.Combine(#"Standardised headings"[Data])

Hope this helps.



Ashish Mathur
Super User
Super User

By using Append Queries, you don't have to do any manual reordering

and the appended results will automatically append the correct columns.




= Table.Combine({#"Table1 (Col Order 1,2)", #"Table2 (Col Order 2,1)"})





Here we have three queries:

  • Table1 (Col Order 1,2)
  • Table2 (Col Order 2,1)
  • Append Table1 + Table2 (Col Order 1,2)







Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors