Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

splitting Values from one Row to multiple rows

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 ...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Icey's avatar
Icey
Community Support
5 years ago

Hi Anonymous ,

 

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.