Forum Discussion
Dynamically splitting one row into multiple rows based on cell-content
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!
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.
2 Replies
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.
