PowerBI Query

[Anonymous]

I would like to display only Max data from this below table in PowerQuery.

tried List.Max or List.Table . But not working. How to extract the max date and display in the same column itself?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

I'm not sure what you want the result to look like.  I don't know what you mean by extract the max date and display in the same column itself ?

Do you want just a single value like this?

Here's the code that generates this from your sample data and you can download this file with the code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcq5DQAgEAPBXogJbB9vLYj+24AE4XA1u1YCg4KQdr4hzR/hUgI/qktz6S7DY9pGmJAuerIP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Date", each Date.FromText(Text.Middle([Column1],4,4) & " " & Text.Start([Column1],2) & " " & Text.Middle([Column1],2,2), "en-US")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", let latest = List.Max(#"Removed Columns"[Date]) in each [Date] = latest)
in
    #"Filtered Rows"

Regards

Phil

[V-lianl-msft]

Hi @Anonymous ,

I think you will need to split the column into day, month and year, then combine back together with a date separator like 31/01/2020 and then convert.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , this in text format (also not in YYYYMMDD, which can give max in text format), try to change the datatype to date and then try. Or create a new column from it making it has a date and then try max.