Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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

parry2k's avatar
parry2k
Super User
5 years ago

Anonymous why wouldn't it fix the issue? If you remove duplicates, you will be left with 3 rows, until you are looking for something different? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Bacause I need 1 row. The oldest by date.

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      5 years ago

      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