Make Power Query (insert data from folder) files delete or ignore first row

[Jyggalag]

Hi all,

I currently have a bunch of files that are 99% similar, except they have some different data in their columns. The first two rows will always look like this:

Unfortunately, the first row messes with the power query, so it returns this:

If I remove the first row, my file works such as intended and I can import my data. However, surely there must be some way to get Power Query to automatically delete or ignore the first row (row A1-X1 etc.) when it loads data, right?

I would truly appreciate some insight here!

Also moderators (@Fluff @Joe4) if I posted this in the wrong forum, apologies! I would like to kindly ask you to move it to the correct forum in such an instance.

Kind regards,
Jyggalag

 

[Jyggalag]

Select the Source step (in the list on the right), then post a picture of that please.

Hi Rory,

I managed to do it now:

I removed the top rows in Transform Sample

However, when I try to refresh my pivot table data in Excel, I still get this error (as if the top row is interferring):

Any idea why?

 

[Jyggalag]

From the Data Source Settings option

Select the Source step (in the list on the right), then post a picture of that please.

 

[RoryA]

However, when I try to refresh my pivot table data in Excel, I still get this error (as if the top row is interferring):
View attachment 56756

Any idea why?

It means exactly what it says. It's not finding a column with exactly that header.

 

[Jyggalag]

I

It means exactly what it says. It's not finding a column with exactly that header.i

I tried to edit it all now:

I have only removed the top row, where I wrote "test" in cell C1 in transform sample.

However, I still get this error in my file:

Any idea why?

 

[RoryA]

It looks like that final changed type step is referring to the original column names from before you removed the top row. Delete that step and then redo any column type changes you need.

Note: given that you have changed all the column names now, there's a good chance you will have to rebuild your pivot table anyway.

 

[Jyggalag]

It looks like that final changed type step is referring to the original column names from before you removed the top row. Delete that step and then redo any column type changes you need.

Note: given that you have changed all the column names now, there's a good chance you will have to rebuild your pivot table anyway.

Can I delete EVERYTHING from my power query builder and then start building it again like before?

And if so, how do I make sure to have the top row deletion inserted?

I am afraid that I am stepping over my own feet at the moment

 

[RoryA]

You can, but I suspect you'll simply have the same issue. The wizard for importing from a folder is still not that user friendly.

What was the problem with just deleting the Changed Type step from that query?

 

[Jyggalag]

You can, but I suspect you'll simply have the same issue. The wizard for importing from a folder is still not that user friendly.

What was the problem with just deleting the Changed Type step from that query?

I have solved the issue now. I'm not sure how to do all of this, Power Query is honestly a big sandbox to me haha, but I appreciate your assistance so much Rory! I could not have done it without you.

I hope that my solution is stable, but essentially what I did was create a new pivot table in a new sheet, and this fixed the old one as well.

My data looks like this now:

I added the "Remove top row" under "Remove rows", just enter the number 1. AFTER this I set top row as header.