Expand Column After GroupBy

[Anonymous]

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

[parry2k]

@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]

Bacause I need 1 row. The oldest by date.

[v-easonf-msft · Accepted solution]

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]

@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]

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?