Power Query: Create column from cell value in table column

[aury_lola]

Hi everyone,

I find myself in a bit of a pickle after trying several options without any success. So here I am with a problem that I sincerely hope someone can help me with.

I imported multiple excel files from a folder, each with multiple sheets all formatted the same way.

After some initial transformations, I arrive at a table identifying the name of each sheet from each file [Model Name], and the relative table column [Excel.Data]. Before expanding the table column, I would like to generate a custom column that reads the specific value in a cell within those tables (the same everywhere), specifically the cell E1 in the excel sheets.

In pictures, here is where I arrive.

Problem

And here is what I would like to get.

Expected solution

The similar post I have seen all related to a cell in multiple files but I couldn't find anything about a cell in all sheets of multiple files. As if this adds a level a complexity I cannot seem to be able to untangle by myself.

Any help and suggestion would be greatly appreciated!

[Anonymous]

Hi,

Based on the description, the method PhilipTreacy  provided should be helpful.

You can also view the following document to learn more information.

Operators - PowerQuery M | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy · Accepted solution]

@aury_lola 

Create the new column with this

= Table.SelectColumns([Excel.Data], "Column5"){0}[Column5]

That code is based on your image which shows each table has 5 columns, the 5th being called Column5, and the data you want being in the first row of that column.

Regards

Phil

[aury_lola]

I love it when the solution end up being something much simpler than whatever I was trying. Thank you so much!