Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group by date and keep all columns

Hi everyone,    I am rather new to Power Bi and i have a question someone might be able to answer. I have a set of entries for the same ID. I only want to keep the tuple with the most recent Chang...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi jama1234

    You could try below M query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIw1zOw1DMyMLQEcoJSE1MqgbRzUWpiSWqKUqxOtJIhkG9ogKTIM08hPL8oG8TKLchJzU3NgykFmWdoQpRSkKkGFsRYbYZsHpIpOkou+XmpcHuBihDqYIYgXIDVJ1iUxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Changed Date" = _t, State = _t, Create = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Changed Date", type text}, {"State", type text}, {"Create", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"max", each List.Max([Changed Date]), type text}, {"all", each _, type table [ID=number, Changed Date=text, State=text, Create=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Changed Date", "State", "Create"}, {"all.Changed Date", "all.State", "all.Create"}),
    #"selectrow"=Table.SelectRows(#"Expanded all",each [max]=[all.Changed Date]),
    #"Removed Columns" = Table.RemoveColumns(selectrow,{"all.Changed Date"})
in
    #"Removed Columns"

    Best Regards,
    Zoe Zhi

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

SethuPower's avatar
SethuPower
Frequent Visitor
6 years ago

Please use Group By in Edit Queries using Advanced Option, Group by column as ID and MAX of Changed Date, and All rows with Column name as "AllRows". Click ok. Now the table will have ID, Date, AllRows columns. Click AllRows column, expand and remove other columns that are not required. Plese try and let know if this works.

Souvik0812's avatar
Souvik0812
Frequent Visitor
3 years ago

In this way, when expanding the AllRows column, it again brings back all rows and the aggregation gets void.

Instead of doing AllRows, in Advance, add all columns in Grouping except the one that needs to be aggregated. In this way it will keep all columns and aggregate the ones need aggregation.

For this specific query here, ID, State and Previous State should be kept in Grouping and Changed Date in Aggregation.

 

In case having loads of columns to be grouped, don't bother to add them one after another. Do it by grouping 1 or 2 and later add all the column names in Advanced Editor.