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 statement, but somehow the filtering doesn't work as planned. So the steps I was thinking about:

1. Create a filtered table (Date = DateOfCurrentRow, Place = PlaceOfCurrentRow) per row

2. Find the max value of the value column and place it in the new column

 

The problem is that I don't know how to create a filtered table based on the contents of the current row. In Dax I would know to use EARLIER but I need to do it in PQ. I hope the question is clear enough.

 

Thank you for your precious time!

 

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])

     

     

     

     

     

5 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    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

      This works like a charm although my refresh has become terribly slow, so I'll have to find a way to fix that. If it isn't too much trouble, can I ask to elbaorate on how this works:

       

      (t)=> t[Place] = [Place] and t[Date] = [Date]

       


      I have never seen this before and I would like to understand it better to up my skills. A link to a doc is also just fine!

       

      Again, thanks a lot, it made my day!

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        4 years ago

        You need to look at the M Code to understand the issue.  When you add a column, the generated M Code uses the `each` keyword.  So in the generator function, when you want to refer to an entire column, you need to specify the table.  In the Table.SelectRows function, (t) will refer to the entire table; so t[Place] refers to the entire column [Place], whereas [Place] by itself, refers to the entry on the same row.

         

        You can read about using "each" in PQ here:  The Each Keyword in Power Query - The Excelguru BlogThe Excelguru Blog