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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.

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

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.

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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors