Forum Discussion

Netrelemo's avatar
Netrelemo
Icon for Helper IV rankHelper IV
4 years ago
Solved

Ungroup a date grouping

I want to "ungroup" a dataset, but I don't know where to start using PowerQuery

 

Convert this: Activities by Financial Period with Start and End Date

 

ProjectID PhaseName  FinPeriodStart FinPeriodFinish
AB315 Closure        2022-06-30     2024-06-30
AB315 Implementation 2022-10-31     2023-07-31
AB315 Investigation  2021-08-31     2022-04-30
AB335 Closure        2020-10-31     2021-09-30
AB335 Design         2020-09-30     2021-03-31
AB448 Closure        2022-03-31     2023-03-31
AB448 Design         2020-02-29     2020-05-31
AB448 Design         2022-04-30     2023-02-28
AB448 Implementation 2022-02-28     2022-05-31

 

 

To this: Activities by Financial Period

 

ProjectID FinPeriod Activities
AB315 2022-10-31 Implementation, Closure
AB448 2022-03-31 Closure
AB335 2020-09-30 Design, Closure
AB335 2021-09-30 Closure
AB326 2021-07-31 Design, Implementation, Closure,
AB331 2021-07-31 xxxxx, etc

 

 

 

The key difference here is that the first one has a start date and an end date, and the second one tells me what is occuring in each financial period (month). 

Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi Netrelemo ,

     

    Is it this you are looking for?

     

    If yes, then this is the code you can use in the advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDLDsIgEEX/hXUThoEqXfrY+A1NFy5IQ9JSI9TvlwbEobrjhnPuwPQ9O52laFnDLtPi16eJJwkcDhwBkQbFhuZL3+bHZGbjwj3YxW2c4AKKJDgctyBryb2MD3YkDugNE3mQSgXZkbtnfSZAprukUvpqvB0dvYc8R1JYKb2vTgDSICu6VGPHAWl1m8JfmPwqmjqbdfPPLguHdELcy/AG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, PhaseName = _t, FinPeriodStart = _t, FinPeriodFinish = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"PhaseName", type text}, {"FinPeriodStart", type date}, {"FinPeriodFinish", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"FinPeriodStart"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"ProjectID"}, {{"ProjectID.1", each _, type table [ProjectID=nullable text, PhaseName=nullable text, FinPeriodFinish=nullable date]}, {"FinPeriodFinish", each List.Max([FinPeriodFinish]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Activities", each Table.Column([ProjectID.1], "PhaseName")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ProjectID.1"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Activities", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

     

    Let me know if this helps! 🙂

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

     

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi Netrelemo ,

     

    Is it this you are looking for?

     

    If yes, then this is the code you can use in the advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDLDsIgEEX/hXUThoEqXfrY+A1NFy5IQ9JSI9TvlwbEobrjhnPuwPQ9O52laFnDLtPi16eJJwkcDhwBkQbFhuZL3+bHZGbjwj3YxW2c4AKKJDgctyBryb2MD3YkDugNE3mQSgXZkbtnfSZAprukUvpqvB0dvYc8R1JYKb2vTgDSICu6VGPHAWl1m8JfmPwqmjqbdfPPLguHdELcy/AG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, PhaseName = _t, FinPeriodStart = _t, FinPeriodFinish = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"PhaseName", type text}, {"FinPeriodStart", type date}, {"FinPeriodFinish", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"FinPeriodStart"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"ProjectID"}, {{"ProjectID.1", each _, type table [ProjectID=nullable text, PhaseName=nullable text, FinPeriodFinish=nullable date]}, {"FinPeriodFinish", each List.Max([FinPeriodFinish]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Activities", each Table.Column([ProjectID.1], "PhaseName")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ProjectID.1"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Activities", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

     

    Let me know if this helps! 🙂

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

     

    • Netrelemo's avatar
      Netrelemo
      Icon for Helper IV rankHelper IV
      4 years ago

      Thanks Tom, 

      Would it be possble to have the "Activities" list sorted as well? I sometimes get a "Design, Closure" instead of "Closure, Design" (alphabetical)