Forum Discussion

melina's avatar
melina
Helper IV
9 years ago
Solved

Query editor filter the latest date

Hi All,

 

In my query editor, i have several of files.

The files are naming based on <plant_filename_date>

In the query editor, there are files from the same plant but different date.

 

Can i have filter in query editor, just for 1 file for the same plant with the lastest date?

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    You can Group By plant with operation "All Rows" and then adjust the generated code to have the maximum date selected.

     

    let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Plant"}, {{"AllData", each Table.Max(_,"Date"), type record}}),
    #"Expanded AllData" = Table.ExpandRecordColumn(#"Grouped Rows", "AllData", {"FileName", "Date"}, {"FileName", "Date"})
in
    #"Expanded AllData"

8 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    You can Group By plant with operation "All Rows" and then adjust the generated code to have the maximum date selected.

     

    let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Plant"}, {{"AllData", each Table.Max(_,"Date"), type record}}),
    #"Expanded AllData" = Table.ExpandRecordColumn(#"Grouped Rows", "AllData", {"FileName", "Date"}, {"FileName", "Date"})
in
    #"Expanded AllData"
    • melina's avatar
      melina
      Helper IV
      9 years ago

      Hi,

       

      I dont have plant and date column.

      What i have just column for source name that contain <plantName_fileName_Date>

      for example <F02_Shipping_19062017>

       

      so do i need to extract this information first?

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        Yes. Propobably the best thing you can do is split the column on each underscore (_) and proceed from there.