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

[ok_kpop]

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

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: 

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

[Ashish_Mathur]

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.

[foodd]

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