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

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.