Power Query - Referencing to cells 1

[arvarr]

Hi
Hoping someone can assist.

1. I am running a power query to extract the file address and also the amount from the columns from a file location
2. Then, i used a vlookup formula to pull through the numbers extracted above

Currently, without using power query, I would reference the cell from the other workbooks and double clicking the cell will open up the workbook.

Is there a method to achieve pulling through the number and also the number in the cell being a hyperlink?

Thanks.

 

[arvarr]

Is there any way for power query to extract cell address?
Instead of the value in the cell address, I would like it to return the cell reference number.
E.g. A1

I have only been able to pull through path and the sheets so far.

Thanks.

 

[combo]

With [tt]Table.AddIndexColumn[/tt] you can add index to your table and next search it keeping index. Knowing top-left cell of searched range and index of matched data there is no problem to build address.

combo

 

[arvarr]

Hi Combo
I have added Index as suggested.
Not sure what you meant by the next step to build address?
What i need is the cell address from the source file.
Thanks.

 

[combo]

Guess that index is related to row in the worksheet, may require adjustment by adding constant. So combining, say, "A" with the query result for modified index you should get addresses.

combo

 

[arvarr]

Hi Combo
After adding index column, it gives me a sequence starting from 1 or whatever number i choose to start with.
A bit confused. if you dont mind elaborating a bit.

E.g. Source file - Data is is cell B24

Thanks.

 

[combo]

If your table starts in row 1, data in row 2:
1) before any filtering, add index to new Col_1, 1 refers to row 2, 2 to row 3 etc.,
2) add new column Col_2, add 1 to Col_1, values in Col_2 correspond to rows in source,
3) add new Col_3, concatenate "B" and Col_2, you get a list of addresses in column "B" in source,
4) delete Col_1, Col_2,
5) process your data keeping data in Col_3.

combo

 

[arvarr]

Hi Combo
I previously added 2 columns
1. Filtered to get Balance
2. Filtered to get $

The problem is that the cell address referencing to "Balance" text and $ amount is never in the same row.

Will the suggestion above work?

Thanks.

 

[combo]

VLOOKUP in PQ is replaced by joining tables. If each table has, just after Source definition, the above row calculation, you can process row IDs separately for each table. The problem can be if the table left-top cell has no fixed address.
If you join the same table, create support query to add only proper row to output and some clean-up data formatting, leave it only as connection. This query can be a source for the data processing (Source=QueryName).

You can also add =ROW() in excel source to get row directly in table.

For query output in the worksheet you can add calculated column, you will get a combination of query result and excel structured table formulas (as HYPERLINK) in single table.


combo