cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ripstaur
Helper III
Helper III

Can I delete superheaders when combining files in a SharePoint Folder?

I have some excel files that are posted on a sharepoint folder every morning. Each one contains information about product shipments. Each file tracks a particular product. So maybe some units of product A was shipped to several locations. 

Unfortunately, the legacy system these files come from put the product and a couple of other "superheader" type information into cells in the top row of the table, creating a superheader. 

I have been able to get them to load, and using the first file as an example, I promote the first row as headers. This gets rid of the superheader row in the example file. However, when the query tries to fetch other files, it looks for the column that had the product information in it and gives me an error saying "field 'product A' not found". 

I had thought that by removing the superheader in the first file, it would do that for every file BEFORE it tries to combine them. That does not appear to be the case. It seems that my only option is to try to get the users to delete that top row before they post to the SharePoint. 

It doesn't seem that it would be a huge leap in technology to make this happen. Am I doing something wrong, or does the function just not work in the right sequence? 

1 ACCEPTED SOLUTION
ripstaur
Helper III
Helper III

I got it to work! It turned out that PQ added its own promote headers step, followed by a type change step. Maybe that's because the files I have to work with have a blank row at the top, then the super-header, then the headers. In any case, that change type step is looking for the text in the superheader as it tries to emulate the code in the example file going forward. I eliminated that change type step, added another promote headers step, and it worked like a champ!

View solution in original post

2 REPLIES 2
ripstaur
Helper III
Helper III

I got it to work! It turned out that PQ added its own promote headers step, followed by a type change step. Maybe that's because the files I have to work with have a blank row at the top, then the super-header, then the headers. In any case, that change type step is looking for the text in the superheader as it tries to emulate the code in the example file going forward. I eliminated that change type step, added another promote headers step, and it worked like a champ!

AlexisOlson
Super User
Super User

"Promote headers" turns the top row into column names, so if you have inconsistent text in the top row of each file, then you'll get inconsistent column names, resulting in errors when trying to combine the files together.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors