Forum Discussion
Expand Column After GroupBy
Good afternoon gurus! I'm having trouble expanding a column, after a groupby has been performed. I need to maintain the grouping as well. My data after the groupby looks like this and the fie...
Hi, Anonymous
You need to expand the "Transaction Date" to get the complete table, then just need to filter the data to keep the data that satisfies "transaction date" equal to "first transaction date".
M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+9CsMwDAZfRXgOWJ+I41RbHiCQPWRofiil0CGQ969Dm6F1sKtR3Ald35sG4DDiTWHEllaYPRFUnDJT04b1bdzWx2KG4puGBR84tLzsfPc375DjBZZlF2qiSp1Xrt7/jNP1/kzi4b6w4nN/nbcfvs7FIsIztTkhyo2FdG+aPwkOwvAC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Schedule" = _t, #"Transaction Date" = _t, #"Login Code" = _t]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Transaction Date", type datetime}}, "en-GB"), #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Order Schedule"}, {{"First Transaction Date", each List.Min([Transaction Date]), Int64.Type}, {"All", each _, type table [Order Schedule=nullable text, Transaction Date=nullable datetime, Login Code=nullable text]}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Transaction Date", "Login Code"}, {"Transaction Date", "Login Code"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"First Transaction Date", type datetime}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ( [Transaction Date]=[First Transaction Date])) in #"Filtered Rows"Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
Bacause I need 1 row. The oldest by date.
Hi, Anonymous
You need to expand the "Transaction Date" to get the complete table, then just need to filter the data to keep the data that satisfies "transaction date" equal to "first transaction date".
M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+9CsMwDAZfRXgOWJ+I41RbHiCQPWRofiil0CGQ969Dm6F1sKtR3Ald35sG4DDiTWHEllaYPRFUnDJT04b1bdzWx2KG4puGBR84tLzsfPc375DjBZZlF2qiSp1Xrt7/jNP1/kzi4b6w4nN/nbcfvs7FIsIztTkhyo2FdG+aPwkOwvAC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Schedule" = _t, #"Transaction Date" = _t, #"Login Code" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Transaction Date", type datetime}}, "en-GB"),
#"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Order Schedule"}, {{"First Transaction Date", each List.Min([Transaction Date]), Int64.Type}, {"All", each _, type table [Order Schedule=nullable text, Transaction Date=nullable datetime, Login Code=nullable text]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Transaction Date", "Login Code"}, {"Transaction Date", "Login Code"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"First Transaction Date", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ( [Transaction Date]=[First Transaction Date]))
in
#"Filtered Rows"
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason