Query editor filter the latest date

[melina]

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?

[MarcelBeug · Accepted solution]

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]

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]

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

[melina]

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

[MarcelBeug]

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:

[melina]

what is file name?

Because i got null value?

[MarcelBeug]

So now you are asking me where you can find the filenames?

That feels like the world upside-down.

It is your data, so you should know where the filename is located.

Otherwise please share a screenshot that shows your column with filenames.

Are you sure you selected this column before splitting by underscore (_)?

Or did you just copy and paste my code somewhere into your query?

That ain't gonna work, that's why I created a video to illustrate the step to take.

[melina]

Ok, i got the solution..!

Sorry and thanks!