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 field I need to expand is called "Login Code".

 

The code used is;

    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order Schedule"}, {{"First Transaction Date", each List.Min([Transaction Date]), type nullable datetime}, {"All", each _, type table [Order Schedule=nullable text, Transaction Date=nullable datetime, Login Code=nullable text, zCompany=nullable text]}})
in
    #"Grouped Rows"

 

Everything I've tried seems to undo the grouping. I have seen a couple of similar questions asked in the forums, but haven't been able to get any of those solutions to work.

 

Any help would be greatly appreciated, cheers!!

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

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous why not expand to "Login Code" and in the next step, do remove duplicates by selection all 3 columns?

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi parry2k, thanks for taking the time to look at this. However unfortuantely the suggestion won't work.

       

      This is the raw data before the grouping.

       

      After the grouping it looks like this (Grouped by the Oldest Date stamp).

       

      This is what it looks like after expanding the field called "Login Code". It seems to be duplicating the Grouped Date stamp by the original 5 "Login Codes".

       

      I only want to see the "Login Code" relevant to the oldest date stamp, which in this case is "gburke"... Removing duplicates won't fix this issue.

       

      Any other ideas?

  • 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