M Language to default to the last number in a list

[ccarpent]

Hello

Is there a way I can use 'M  language' to choose the last number in my column named 'Index'?

This basic layout, cretaed in PowerQuery from an excel sheet, is to allow me to update the version number used on each page in particular report.  The Blue and Green boxes are filtered e.g. last date and report name.  The Index field is me using PowerQuery index option, but I made two changes but it woudl be good to select the largest number in the index column.

Chris

[edhans]

Filter by the largest value on that list. just select the 6 manually.

Then edit the M code Power Query generates. Change:

Table.SelectRows(Source, each [Index] = 6)

to

Table.SelectRows(Source, each [Index] = List.Max(Source[Index]))

 Source is the name of the previous step, and [Index] is the column. You can just use the same Previous Step name that is the first value in the Table.SelectRows() function.

[ccarpent]

Hi Edhams

I think I am not doing this correct, as I get an "Expression.SyntaxError: Token Comma expected" error message.

So I changed my code to this:

let
Source = Excel.Workbook(File.Contents("Z:\PowerBI\_lookuptables_v3\version.xlsx"), null, true),
Version_Table = Source{[Item="Version",Kind="Table"]}[Data],
#"Added Index" = Table.AddIndexColumn(Version_Table, "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", let latest = List.Max(#"Added Index"[Date_Ver]) in each [Date_Ver] = latest),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Index", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Index] = List.Max(#"Changed Type"[Index]))
in
#"Filtered Rows1"

The "Index" column is set as a Whole Number.

[edhans · Accepted solution]

It is a misleading message. You need a 3rd closing paren on the last line, not a comma.

let
Source = Excel.Workbook(File.Contents("Z:\PowerBI\_lookuptables_v3\version.xlsx"), null, true),
Version_Table = Source{[Item="Version",Kind="Table"]}[Data],
#"Added Index" = Table.AddIndexColumn(Version_Table, "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", let latest = List.Max(#"Added Index"[Date_Ver]) in each [Date_Ver] = latest),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Index", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Index] = List.Max(#"Changed Type"[Index])))
in
#"Filtered Rows1"

The advanced editor is pretty good with this. Below is your original code pasted in. You can see as you select a parenthesis, it will show you the partner. When you select the last paren in the last row of code ,you can see it is paired with the one next to "each" and the Table.SelectRows() function is missing its closing paren.

[ccarpent]

Hi 

Its worked a treat thanks for the help.

Chris

[edhans]

Glad we were able to get that fixed @ccarpent 

[camargos88]

Hi @ccarpent ,

You can create a new column using:

if [Index] =  List.Max(#"StepName"[IndexColumn]) then 1 else 0 

and filter the value 1.

Replace the bold name for your stepname in query steps.

[flowgistics]

Hello @camargos88, could you please explain how your suggestion would work as full line command? I tried to use:

• Table.AddColumn(#"StepName", "findLastIndex", each if [Index] = List.Max([Index]) then 1 else 0) - Error Message Value "1" can't be converted to type "List".
• Table.AddColumn(#"StepName", "findLastIndex", each if [Index] = List.Max(#"StepName"[Index]) then 1 else 0) - Expression SyntaxError RightParen.

Actually I would like to make a True/False Column regarding the Source.Name of a folder import, to find the Source.Name of the latest file loaded and compare all rows to that name..

I would appreciate any help, and hope that an advanced user (compared to me) sees the problem on first sight.

Thanks a lot in advance!