Extracting named cells from multiple sources

[Darrin]

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

[Darrin]

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

[Darrin]

This is what I get moving onto step 3:

[Darrin]

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:

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

[v-jingzhang]

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

[Darrin]

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]

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.

[Darrin]

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:

[artemus]

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]

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]

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.