CODEWITHSUNDEEP
arraysexcelpowerquery
Jan Horčička
Jan Horčička

Reputation: 853

Excel - array formula in a table

I have an Excel sheet with data from power query, that are loaded to the sheet. They are loaded automatically as a table (not simple range).

In that table, I have a list of names (many duplicates) and a date. I would like to create another column that will display the latest date for each name (see image below).

I could do that easily using array function ={MAX(IF.... }, but Excel does not allow to use array in tables. I can't convert it to range, because then it wouldn't update properly.

Is there a way to do it on the worksheet? If not, is there a way to do it in Power Query?

Example

UPDATED:

When I try to use an array formula I get following error (using of course CTRL + SHIFT + ENTER to submit the formula). enter image description here

Upvotes: 0

Views: 8848

Answers (1)

JJB
JJB

Reputation: 53

Here is a pure PowerQuery solution. Might be faster and a little cleaner.

Given this starter table:

input table

Name it "input" then import to the Query Editor. Go to Advanced Editor and paste this code:

let

//Bring in the source table and adjust the type
    Source = Excel.CurrentWorkbook(){[Name="input"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),

//Group on name, keeping all the rows
    Group = Table.Group(Type, {"Name"}, {{"Date", each _, type table}, {"Latest Date", each List.Max([Date]), type date}}),

//Expand the nested table and assign type
    Expand = Table.ExpandTableColumn(Group, "Date", {"Date"}, {"Date"}),
    Type2 = Table.TransformColumnTypes(Expand,{{"Date", type date}})

in
    Type2

To get a returned table that only shows Name and Latest Date, here is the shortened code. Hope it's helpful. Jake

let

//Bring in the source table and adjust the type
    Source = Excel.CurrentWorkbook(){[Name="input"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),

//Group on name
    Group = Table.Group(Type, {"Name"}, {{"Latest Date", each List.Max([Date]), type date}})
in
    Group

Upvotes: 1

Related Questions