Ungroup a date grouing

[Netrelemo]

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

I asked this question previously, got an answer, accepted it as a solution, but then found it didnt actually work.
https://community.powerbi.com/t5/Desktop/Ungroup-a-date-grouping/m-p/2377244#M854570
So I have asked this again.

In my raw dataset I have 7,500 rows.
After transformation, I expect to see a lot more than 7,500 rows.

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

ProjectID PhaseName FinPeriodStart FinPeriodFinish
AB315 Design 2022-06-30 2022-10-31
AB315 Implementation 2022-07-31 2022-09-30

To this: Activities by Financial Period

ProjectID FinPeriod Activities
AB315 2022-06-30 Design
AB315 2022-07-31 Design
AB315 2022-08-31 Design
AB315 2022-09-30 Design
AB315 2022-10-31 Design
AB315 2022-07-31 Implementation
AB315 2022-08-31 Implementation
AB315 2022-09-30 Implementation

And then, group by ProjectID and finPeriod

ProjectID FinPeriod Activities
AB315 2022-06-30 Design
AB315 2022-07-31 Design, Implementation
AB315 2022-08-31 Design, Implementation
AB315 2022-09-30 Design, Implementation
AB315 2022-10-31 Design

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Netrelemo · Accepted solution]

Thank you, much apreciated 

[Ashish_Mathur]

You are welcome.

[AlexisOlson]

You can create a list of dates between start and end and pick just the month ends, then expand that list into new columns.

Here's how to construct the list:

Here's the full query you can paste into the Advanced Editor of a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNjRV0lFySS3OTM8DMowMjIx0Dcx0jQ1gHEMDXWNDpVgdhGLP3IKc1NzUvJLEksx8hCZzkDoYxxJkQmwsAA==", 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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Activities", each List.Select(List.Dates([FinPeriodStart], Duration.Days([FinPeriodFinish] - [FinPeriodStart]) + 1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"FinPeriodStart", "FinPeriodFinish"}),
    #"Expanded Activities" = Table.ExpandListColumn(#"Removed Columns", "Activities")
in
    #"Expanded Activities"