cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smjzahid
Helper IV
Helper IV

Table.RemoveMatchingRows function not removing duplicate headers from multiple excel files

I am using the built in Combine Files  (via the GUI) (function in Power Query) to append multiple xlsx. files into 1 single fine, It works and does the job. However, with this method I get duplicate headers in my visual, shown in the snip

 

Then I used the Table.RemoveMatchingRows (Table Function) to get rid off the duplicated headers, it do not throw any error in Applied step as well as I checked the Advanced Editor and the query compiles successfully, I refreshed the model applied the changes, but still it shows the duplicated header.

 

How do I get rid off the duplicated headers  

 

 RD = Table.RemoveMatchingRows(#"Expanded Table Column1",{[Column1= "Column1",Column2= "Column2",Column3= "Column3",Column4 = "Column4",Column5= "Column5",Column6= "Column6",Column7= "Column7",Column8= "Column8",Column9= "Column9",Column10= "Column10",Column11= "Column11",Column12= "Column12",Column13= "Column13",Column14= "Column14",Column15= "Column15",Column16= "Column16",Column17= "Column17",
Column18= "Column18",Column19= "Column19",Column20= "Column20",
Column21= "Column21",Column22= "Column22"]}),

 

image.png

 

 

 

image.pngimage.png

2 REPLIES 2
watkinnc
Super User
Super User

You could Promote Headers before combining, or just right click on any of the header values in your table and choose "Does not equal". Problem solved!

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Vera_33
Super User
Super User

Hi @smjzahid 

 

From your M code, I can see the header with all "Column" were removed - "Column1", "Column2"...so you want to combine the tables from different files, do they have the exactly same headers? If yes, try to add a custom column, promote the header then combine. And you need to keep the data source, you can add a custom column in each table before combine them.

 

https://community.powerbi.com/t5/Power-Query/Import-Query-Sample-File-from-Folder-doesn-t-read-data/...

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors