Using Row Numbers in Expressions

[Charley]

Is there a Row Number function in Power Query? Or do I need to insert an Index column?

 

[Rudi]

I don't think there is a direct function for getting the row number; and an index column (1-based) will probably be easiest to use.

One formula that comes to mind though is: Table.PositionOf
This works with a filter though (at least how I've used it!)

Something like this:

let
    Source = Csv.Document(File.Contents("C:\Test\MyFile.csv),[Delimiter=",",Encoding=1252]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = "TRIAL BALANCE")),
    MyRow= Table.PositionOf(Source,#"Filtered Rows"{0}),
    ...

 

[Charley]

I just tried...
= Table.AddColumn(#"Removed Columns", "Custom", each Table.PositionOf(Source,#"Removed Columns"{0}))
...and got a column filled with -1.

There's probably a syntax error, but I won't sweat it. Adding an index column is cleaner, I think.

Thanks, Rudi.

 

[Rudi]

I doubt you can use the Table.PositionOf function like that.

The function returns a value (ie: the position of the filtered item in the table; which is the row number where the item was located). Using it outside of a filter and in an AddColumn function is not the correct scenario for it.

I would agree with you that an index column would better suffice your current scenario.

 

[Charley]

Thanks, Rudi.

By the way, here was the problem I wanted to solve...

When using Data, Get Data, From File, From Folder--where the source is a range name rather than a Table--the first row of the Source.Name column contains a file name rather than a column title. Therefore, when I Promote Headers, the specific file name becomes a column title. That's not a problem for single use. But if I want to re-use the query, it means that the folder ALWAYS must have that one file name in it so it can be used as the example file.

To handle that problem, I had two choices, one solution would have been to add an example file with no data, a file that always would be in the folder and used as the example. The other solution was to change the contents of the first row in the Source.Name column to the label I wanted for the column title...doing so before I promoted the headers. Because I couldn't find any way to change a single value directly, I created a new column with the data I wanted:

#"Added Index" = Table.AddIndexColumn(#"Expanded Table Column1", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=1 then "Loc" else [Source.Name]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),

Later, I removed the Source.Name column and used my new "Loc" (Location) column instead.

As a consequence, when I copy my M code to Notepad and search for "xls", nothing is ever found. That is, no unique file name ever appears in my code.

Charley

 

[Rudi]

In the event that I need to promote the first row and I always need the first column to be a constant name, I use this line;

Promoted = the name of the previous step
<name> = the name you want to give the first column (to make it constant)


[FONT=&quot]= Table.RenameColumns(Promoted,{{Table.ColumnNames(Promoted){0}, "<name>"}})[/FONT]

For example, you can name the first column "File Name" to replace the actual files name.
As the query iterates through the other files, the name will always become "File Name"

An example...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Promoted = Table.PromoteHeaders(Source),
    RenCols = Table.RenameColumns(Promoted,{{Table.ColumnNames(Promoted){0}, "<name>"}})
in
    RenCols

 

[Charley]

Rudi,

Ah, ha! The key insight I was missing is that I can reference column names by index number so that I don't need to name them explicitly in the code.

Thanks, again, Rudi!

Charley