Dynamically splitting one row into multiple rows based on cell-content

[droecher]

Hello All,

fairly new to PowerBi / PowerQuery and struggeling with a transformation. The CRM provides an overview of deals, including revenue, when the service/project is planned to start and for how long it will run. 

Simplified Output of CRM
DealID [PK]	CustomerID	TotalRevenue	ServiceStartDate	CountMonthsOfService
1	1	100.000	Jan 1st 2023	4
2	2	40.000	Feb 1st 2023	1
3	2	150.000	Jan 1st 2023	3

In order to do some sort of revenue modelling / prediction, I would neet the following table in PowerBI (assuming revenue is evenly distributed across the whole service/project period and mothly granularity is good enough) (both assumptions may be invalid in the future but are valid at the moment.

Needed Format
DealID	CustomerID	Period	Revenue
1	1	Jan 2023	€ 25.000
1	1	Feb 2023	€ 25.000
1	1	Mar 2023	€ 25.000
1	1	Apr 2023	€ 25.000
2	2	Feb 2023	€ 40.000
3	2	Jan 2023	€ 50.000
3	2	Feb 2023	€ 50.000
3	2	Mar 2023	€ 50.000

Any help, hints, suggestions or solutions are very welcome, as I am struggeling to find the right approach.

Thank you!

[droecher]

Thank you @ImkeF ,

works  wonderfully - and much simpler than all my "join/filter" failures.

[ImkeF · Accepted solution]

Hi @droecher ,

this can be solved by creating a list of relevant months for each row and then derive the date by adding the month index to the start date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWADAyDplZinYFhcomBkYGQM5JooxepEKxkBWSBsAlLhlpqErMIQrMIYqsLQFIshxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DealID [PK]" = _t, CustomerID = _t, TotalRevenue = _t, ServiceStartDate = _t, CountMonthsOfService = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DealID [PK]", Int64.Type}, {"CustomerID", Int64.Type}, {"TotalRevenue", Int64.Type}, {"ServiceStartDate", type text}, {"CountMonthsOfService", Int64.Type}}),
    #"Add list of months" = Table.AddColumn(#"Changed Type", "ListOfMonths", each {1..[CountMonthsOfService]}),
    #"Expanded ListOfMonths" = Table.ExpandListColumn(#"Add list of months", "ListOfMonths"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded ListOfMonths","st","",Replacer.ReplaceText,{"ServiceStartDate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"ServiceStartDate", type date}}),
    #"Add Period" = Table.AddColumn(#"Changed Type1", "Period", each Date.AddMonths([ServiceStartDate],[ListOfMonths]-1)),
    #"Inserted Division" = Table.AddColumn(#"Add Period", "Revenue", each [TotalRevenue] / [CountMonthsOfService], type number)
in
    #"Inserted Division"

 Pls also check file enclosed.