Power Query from multiple Excel files

[bullit_nl]

Hello all,

I would like to create a Power Query from multiple different Excel files.
My problem is the layout of the Excel files. Not all files are having the same layout and all are having a few rows above their headers with a logo and or text.

1.
In the rows above the headers there's text which also has a date (as part of a text). How do I get the date (as a date format) in my Pivot Table?
2.
How do I sort similar columns to the same position?


Or shouldn't I mix up the different Excel files and create a Power Query for each separate?

Thank you.

 

[Zaoka]

I would create 2 power queries for every file.

First query would only load that date filed
Second would load rest of data.

Then i would Append second with First.

Then Merge all append queries

 

[bullit_nl]

Hi Zaoka,
Above my column headers I have 2 rows. One with a text string that contains a date.
How can I extract this text date into a column (as date format) before removing the top 2 rows in the Transform Sample File?

Thank you

 

[bullit_nl]

Hi Zaoka,
Above my column headers I have 2 rows. One with a text string that contains a date.
How can I extract this text date into a column (as date format) before removing the top 2 rows in the Transform Sample File?

Thank you

The problem is that there isn't any date column in this Excel files. Also in the file name there's no date. The only date that is available is above the column headers.

Row 1
Cell A1 has the text with date
(the rest of row 1 is empty)

Row 2
Row 2 is empty

Row 3
Here are the column headers

 

[Zaoka]

maybe add index row

and then filter row 0 only, and delete rest all columns except first one with date

 

[bullit_nl]

maybe add index row

and then filter row 0 only, and delete rest all columns except first one with date

There is no column date yet that's the problem. I need to extract the date from a single cell which contains a text (ex. today's list 05-09-2022) to a column.

 

[bullit_nl]

After some more research I founded this link:

Convert a Cell Value into a Column with Power Query

I tried these steps but unfortunately it didn't work. My situation is also a little bit different.

1. My Date Row is at already the Top
2. My cell isn't a date but a standard cell with text (today's list 05-09-2022)

Maybe someone can help to get it done with the above link.
In the comments of the link they also say it should be possible with right click on the cell and the select drill down.
On my right click there isn't such option.

Thank you

 

[Zaoka]

Text.End([That cell with text], 10)

Will extract only 05-09-2022,

and then use Date.fromText function

Date.FromText - PowerQuery M

Learn more about: Date.FromText

docs.microsoft.com

 

[bullit_nl]

Could you please explain what you mean a bit more detailed or in steps. Sorry but i'm no expert on this.

Thank you