Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ok_kpop
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?

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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

Ashish_Mathur_0-1693028851108.png

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.

Ashish_Mathur_1-1693028987132.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
foodd
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)

foodd_0-1693006640709.png

 

foodd_1-1693006654736.png

 

 

foodd_3-1693006705694.png

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.