Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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?
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:
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.
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:
https://learn.microsoft.com/en-us/power-query/append-queries
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
165 | |
86 | |
68 | |
68 | |
58 |