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.

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        Well, if you use DAX code in the query editor, it isn't going to work.

         

        This would be Power Query code:

         

        let
    Content = Table.FromColumns({{"F02_Shipping_19062017","F02_Shipping_19062018","F03_Shipping_19062017"}},type table[Name = text]),
    #"Split Column by Delimiter" = Table.SplitColumn(Content, "Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}, {"Name.3", Int64.Type}})
in
    #"Changed Type"

         

        And this is how the last 2 steps were created: