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

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)

https://learn.microsoft.com/en-us/power-query/append-queries

Hi,

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

let
    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])
in
    Custom1

Hope this helps.