Reference table properties name & row number

[andydtaylor]

Hi

For purpose of extraction to Power Query I would like to add columns to my tables with table name and row number. What are the simplest cleanest formulas I can use to do this? Presently hard coding table name and using row()-x formula for row number. My preference would be to somehow reference table properties directly.

Thanks


Andy

 

[Ron Coderre]

Here's a "sideways" approach

- I created a table named Table1 in A1:B18

E1: =Table1[#All]

- This formula references the formula in E1 and parses out the table name:

F1: =MID(LEFT(FORMULATEXT(E1),FIND("[",FORMULATEXT(E1))-1),2,100)

which returns: Table1

- I added a column named TableName to my table and put this formula in it:

=$F$1

Is that something you can work with?

For the row number, couldn't you just have power query add an Index column?

 

[andydtaylor]

Hi

Thanks for your suggestions - will evaluate. Yes I can add index in PQ but I am seeking to create a visible audit trail within a financial model. Embedding logic in PQ makes this audit trail less transparent.

Thanks