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 h...
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"
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.

      • melina's avatar
        melina
        Helper IV
        9 years ago

        Sorry, Could you help me to split for the plant and date? I had tried, but got the error.