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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Darrin
Helper I
Helper I

Extracting named cells from multiple sources

Hi,

   I have a number of project management templates that contain info such as project number, start and end date, RAG status etc. All files are in .xls format, sit in the same network folder, and each  info item has a cell name (e.g. 'ProjectNo').

 

   I'd like to use Power Query to extract selected named cells from all files in the folder into a single table sorted on ProjectNo, so that I have a summary report of RAG status etc for all projects in the folder. This will then be used for input into a PowerBI based report.

 

  Can this be done?

 

Darrin

10 REPLIES 10
Darrin
Helper I
Helper I

And end up with this on Step 4, which doesnt put the named cells as columns:

 

Darrin_1-1652882595597.png

 

 

Darrin
Helper I
Helper I

This is what I get moving onto step 3:

Darrin_0-1652882254801.png

 

Darrin
Helper I
Helper I

Hi - many thanks for the solution. I managed to go all the way through without error, but only getting a table with a list of the files, creation date etc...not the slected cells.  

 

Can I clarify Step 2?  When I start to import the file into a query, I get this screen:

 

Darrin_0-1652260238666.png

 

Do I select my chosen data items at this point and do I use load or transform.

 

What I am not getting automatically is seeing the gear next to the source step.

 

Darrin

Hi @Darrin 

 

What are the positions of the selected named cells? Are they always in a sheet with a specified sheet name and are at the same positions in all files? 

 

In step 2, you will see all sheets and tables in a file. You need to select the sheet where you want to extract data from and click transform data to go on transforming it. 

 

Best Regards,
Community Support Team _ Jing

Thanks. I'm still having problems and I think it might be step 2 that is doing it.  

As per my previous attached pic, If I select just the sheet 'one pager' I get presented with a table with multiple unnamed colums, but I can see the data is visible in the tabke, but not against the cell reference names I want.  If I choose a small number of cell names in step 2, then I dont get visible data at all.

artemus
Microsoft Employee
Microsoft Employee

The top entries in step 2 are the sheet data... the items below that (with a different icon) are the tables in that sheet. If you just need one of those tables you can use that. If you need multiple tables, but they are independent, you can repeat these steps for each one. If you need to combine the tables in some way, then the solution becomes more complex and what you need to do depends on how these tables interact with each other.

Hi, thanks for being patient!.  

 

Yes, there are three sheets.  I only want to pick up data from the first sheet as the second two are contain lookups and notes.

 

The pronlem I have is that 'One page Summary' isn't formated as a table. I need to select fields to convert into a query table from a small sample of the named celss you can see below the sheets.

 

A small sample of the One Page Summary sheet below:

Darrin_0-1652947919271.png

artemus
Microsoft Employee
Microsoft Employee

You need to piece together a single row from this table like:

Table.FromRecords({[#"Project Status" = PreviousStep[Column6]{1}, ...]})

 

Where Column6 is the column with the value for Project Status, and 1 is (row number - 1) of that value. Repeat this for all items in the table (Hopefullly no one moved stuff around in other workbooks). If done correctly you should have a table with 1 row.

artemus
Microsoft Employee
Microsoft Employee

Make sure you are loading the data from a file, not the current workbook. Just transform the data.

 

You seem to be missing step 8 where you invoke the function to get to the data. 

artemus
Microsoft Employee
Microsoft Employee

This is a bit involved...

 

1. Create a new Parameter, called FileName, type = Text, currentValue = <File name, excluding folder, of any one of the Excel files (e.g. example.xlsx)>

2. Import the Excel file into a query.

3. Click the gear next to the source step. Clicked advanced. In the location field, delete the file name so only the folder remains. Then add a part, click the ABC button to the left and change it to parameter. Set the parameter as FileName. Click ok

4. Right click your query and choose "Create function". Give the function a name like "Import from Excel".

5. In your origional query, transform until you get the data you want.

6. Add a new query using the "Folder" data source.

7. Select the folder that has the excel fiels.

8. Click the Name column, and under Add column, choose "Invoke custom function".

9. Choose the function that was created.

10. If you see no errors in the new column, you should be able to click the combine tables button in the column header.

11. Right click and choose "Drill down" on the single cell in that column.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors