Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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
Proud to be a Super User!
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.
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
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!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
59 | |
43 | |
40 |