Forum Discussion

Benjamin_Eureka's avatar
Benjamin_Eureka
Frequent Visitor
4 years ago
Solved

Create conditional column based on table filtered by row data

Hi folks,   I'm trying to create a conditional column with the max value of a certain place on a certain date (see image), but I can't get it to work. I tried to nest a filtered table in a List.Max...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    Here is one method

     

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Place", type text}, {"Value", Int64.Type}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Max", each 
      let 
        tb= Table.SelectRows(#"Changed Type", (t)=> t[Place] = [Place] and t[Date] = [Date])
      in  
        List.Max(tb[Value]))
in
    #"Added Custom"

     

     

     

    If you are just using the UI,   Add a custom column with the formula:

    let 
        tb= Table.SelectRows(#"Changed Type", (t)=> t[Place] = [Place] and t[Date] = [Date])
      in  
        List.Max(tb[Value])

     

     

     

     

     

Benjamin_Eureka's avatar
Benjamin_Eureka
Frequent Visitor
4 years ago

Hi Ron,

 

Thanks for the reply! The slow query led me on a journey to speed optimisations for PQ. After I placed the step before the solution step in a Table.Buffer the query was way faster, the refresh time was 30 seconds now, but for a thousand rows this is still awefully slow. I came across this website Performance aspects for PQ and at some point I wondered if I just could have used a 'Group by' with the advanced option of 'All rows'. And it worked! Now it only takes a second or 2 to refresh. So bottom line: this has been an educating experience! 

 

Thank you for your time and efforts, it's well appreciated! 

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

Yes. That is definitely a useful reference when the need arises.