Finding values in a row in PowerBI

[leeedney]

Hi everyone,

I have a table in PowerBI in a format similar to:

Project ID       Milestone1       Milestone2       Milestone3       Milestone4      Milestone5

1                     01/04/17          01/05/17          02/07/17          09/09/17          01/12/18

2                     17/04/17          11/06/17          02/08/17          09/10/17          26/12/18 

...

What I want to do is create a new column that works out which stage each project is at, by comparing it to a static measure. In Excel I'd do an INDEX MATCH (less than) for each row that returned the top row of data. Is it possible to do that when you're actually asking PowerBI to return the column name?

Open to any suggestions.

Thanks,

-Lee

The excel code would be:

=INDEX($A$1:$F$1,1,MATCH(LookupDate,$A2:$F2,1))

[MarcelBeug · Accepted solution]

I created a table in Excel according to your example and created the following code in the Query Editor.

Edit: I also created parameter StaticMeasure with type date.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Finding values in a row in Power BI.xlsx"), null, true),
    Projects_Table = Source{[Item="Projects",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(Projects_Table,{{"Project ID", Int64.Type}, {"Milestone1", type date}, {"Milestone2", type date}, {"Milestone3", type date}, {"Milestone4", type date}, {"Milestone5", type date}}),
    #"Added Custom" = Table.AddColumn(Typed, "Stage", each List.Union({{"No Milestone yet"}, List.Skip(Record.FieldNames(_))}){List.Count(List.FirstN(List.Skip(Record.FieldValues(_)),each _ <= StaticMeasure))})
in
    #"Added Custom"