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 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?