splitting Values from one Row to multiple rows

[Omarwaleedazab]

hello Power Bi Community 

i hope if anyone can help me reach this.

What im trying to achive is a dynamic way for spliting this row into multiple rows per each day to have the below report as I can only do it manully by dividing the value over the days and getting each day value.

in the attached image you will find the table in blue is the orginal row 

while the table in orange the report that i want to reach.

so if anyone can help me with it through excel or power query or something will be pleased.

and here you can find all the fourmuls used in my table.

Thank you guys,

[Icey]

Hi @Omarwaleedazab ,

Please check:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYo7DoQwDESvEqXemPEnS6ADChoi0WyFuP81cBYkmrHnzTuOCCo5fuK+BYZfgXCXOw4sIxCm+rIcbMwPMpK/bfOPU53TsiatCgi+jtk8QDrcjodP7e+fBRi0iVKIG6bSmirB4nle", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Eff." = _t, PL = _t, #"Start Date" = _t, #"End Date" = _t, Days = _t, #"FG Code" = _t, Speed = _t, #"SF WT" = _t, #"BOM Qty" = _t, #"BackOrder Qty" = _t, #"Prod Per Day" = _t, #"Total SF Weight" = _t, Tons = _t, #"Ton pull Per Day" = _t, #"Tons Pull per Days" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Eff.", type number}, {"PL", type text}, {"Start Date", type datetime}, {"End Date", type datetime}, {"Days", type number}, {"FG Code", type text}, {"Speed", Int64.Type}, {"SF WT", type number}, {"BOM Qty", Int64.Type}, {"BackOrder Qty", Int64.Type}, {"Prod Per Day", Int64.Type}, {"Total SF Weight", type number}, {"Tons", type number}, {"Ton pull Per Day", type number}, {"Tons Pull per Days", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "StartDate_", each let rows_ = Number.RoundUp([Days]) in List.DateTimes([Start Date],rows_, #duration(1, 0, 0, 0))),
    #"Expanded StartDate_" = Table.ExpandListColumn(#"Added Custom", "StartDate_"),
    #"Added Custom1" = Table.AddColumn(#"Expanded StartDate_", "EndDate_", each let EndDate_ = [StartDate_]+#duration(1,0,0,0) in if EndDate_>[End Date] then [End Date] else EndDate_),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Days_", each Number.Round(Duration.TotalDays([EndDate_]-[StartDate_]),1)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "BackOrder Qty_", each [Days_]*(([Speed]*1440*[#"Eff."])/[BOM Qty])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Tons_", each try [BackOrder Qty_]*[Total SF Weight] otherwise 0),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Tons Pull per Days_", each ([Speed]*[SF WT]*1440)/1000*[Days_]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Start Date", "End Date", "Days", "BackOrder Qty", "Tons","Tons Pull per Days"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Eff.", "PL", "StartDate_", "EndDate_", "Days_", "FG Code", "Speed", "SF WT", "BOM Qty", "BackOrder Qty_", "Prod Per Day", "Total SF Weight", "Tons_", "Ton pull Per Day", "Tons Pull per Days_"})
in
    #"Reordered Columns"

 

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL]

Excel formula can't dynamically extend rows by itself whereas Power Query can.

If you wanna get your issue quickly resolved or at least, get some instructive advice, you'd better paste some Excel or pbix files with enough mockup data instead of pics; what's more, input manually some desired results with simple calculations with numbers instead of obscure formulae.