Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
droecher
Regular Visitor

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]CustomerIDTotalRevenueServiceStartDateCountMonthsOfService
11100.000Jan 1st 20234
2240.000Feb 1st 20231
32150.000Jan 1st 20233

 

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
DealIDCustomerIDPeriodRevenue
11Jan 2023  25.000
11Feb 2023  25.000
11Mar 2023  25.000
11Apr 2023  25.000
22Feb 2023  40.000
32Jan 2023  50.000
32Feb 2023  50.000
32Mar 2023  50.000

 

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

Thank you!

1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
droecher
Regular Visitor

Thank you @ImkeF ,

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

ImkeF
Community Champion
Community Champion

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082