Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
aury_lola
Regular Visitor

Power Query: Create column from cell value in table column

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.

ProblemProblem

And here is what I would like to get.

Expected solutionExpected 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!

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

@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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

@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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.