Divide Column Values to New Columns

Here is an example code that transforms this example data...

to...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hYw7DsAgDEPvwowiO+HXqxRx/2s0tAx0qLrYjpT3eg9UyyGG0zwOEPRutMQyhwDwpoCi0PkJFW3PMeLNl0++Lh5pF2QxvgR1F+i/oIhhCcYF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Promo ID" = _t, #"Promo Type" = _t, #"Store ID" = _t, #"Product ID" = _t, Forecast = _t, Start = _t, End = _t]),
    set_types = Table.TransformColumnTypes(Source,{{"Promo ID", type text}, {"Promo Type", type text}, {"Store ID", Int64.Type}, {"Product ID", Int64.Type}, {"Forecast", Int64.Type}, {"Start", type date}, {"End", type date}}),
    generate_date_scope = List.Generate(()=>List.Min(set_types[Start]), each _ <= List.Max(set_types[End]), each Date.AddMonths(_, 1), each Date.ToText(_, [Format="MMMMM yyyy"])),
    add_promo_dates = 
    Table.AddColumn(
        set_types, 
        "_promo_period_dates", 
        each 
            let 
                start = [Start], 
                end = [End] 
            in 
                List.Generate(
                    ()=>start, 
                    each _ <= end, 
                    each Date.AddMonths(_, 1), 
                    each _
                ),
        type list
    ),
    add_promo_days = Table.AddColumn(add_promo_dates, "_days_in_periods", each List.Transform([_promo_period_dates], each Date.DaysInMonth(_)), type list),
    transform_promo_lists = Table.TransformColumns(add_promo_days, {{"_days_in_periods", each List.Transform(_, (r)=> r/List.Sum(_)), type list}, {"_promo_period_dates", each List.Transform(_, each Date.ToText(_, [Format="MMMMM yyyy"])), type list}}),
    zip_lists = Table.AddColumn(transform_promo_lists, "_dates", each let fore = [Forecast], transformList = List.Transform([_days_in_periods], each Number.ToText(Number.Round(_ * fore,4))) in List.Zip({[_promo_period_dates], transformList}), type list),
    convert_lists_to_table = Table.TransformColumns(zip_lists, {{"_dates", each Table.PromoteHeaders(Table.Transpose(Table.FromList(List.Transform(_, each Text.Combine(_, "|")), Splitter.SplitTextByDelimiter("|"), {"Month Year", "Portion"}))), type table}}),
    remove_columns = Table.RemoveColumns(convert_lists_to_table,{"Start", "End", "_promo_period_dates", "_days_in_periods"}),
    group_rows = Table.Group(remove_columns, {"Promo Type", "Store ID", "Product ID"}, {{"Promo ID", each Text.Combine(_[Promo ID], ";")}, {"Combined Tables", each Table.FirstN(Table.FillUp(Table.Combine(_[_dates]), Table.ColumnNames(Table.Combine(_[_dates]))),1)}}),
    expand_table = Table.ExpandTableColumn(group_rows, "Combined Tables", generate_date_scope)
in
    expand_table

It is a bit clunky but should get you pointed in the right direction.

Hi jamuka, another solution:

 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lctLDoAwCATQu7BuyAz0o1ex6f2vIdUYdeHCDcMQXu9C8yJJNo+xgmDkQs+sc1EAkVRQDTY/LY52lpEOXz99u32+vEcpL9+e3v74sQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Promo ID" = _t, #"Promo Type" = _t, #"Store ID" = _t, #"Product ID" = _t, Forecast = _t, Start = _t, End = _t]),
    ChangedTypeEN = Table.TransformColumnTypes(Source,{{"Forecast", type number}}, "en-US"),
    ChangedTypeSK = Table.TransformColumnTypes(ChangedTypeEN,{{"Start", type date}, {"End", type date}}, "sk-SK"),
    fn_Date = each Date.ToText(_, [Format="MMMyyyy", Culture = "en-US"]),
    AllDates = [ a = List.Buffer(List.Generate(
        ()=> Date.StartOfMonth(List.Min(ChangedTypeSK[Start])), 
        each _ <= Date.StartOfMonth(List.Max(ChangedTypeSK[End])),
        each Date.AddMonths(_, 1),
        each fn_Date(_) ) ),
    b = Record.FromList(List.Repeat({null}, List.Count(a)), a )
  ][b],
    StepBack = ChangedTypeSK,
    GroupedRows = Table.Group(StepBack, {"Promo Type", "Store ID", "Product ID"}, {{"T", each
      [ // _Detail = GroupedRows{[#"Promo Type"="Z3",#"Store ID"="90101",#"Product ID"="813416"]}[All],
        _Detail = _,
        Ad_R = Table.AddColumn(_Detail, "R", each 
            [ a = Duration.Days([End]-[Start])+1,
              b = List.Generate( ()=> Date.EndOfMonth([Start]), (x)=> x <= Date.EndOfMonth([End]), (x)=> Date.EndOfMonth(Date.AddMonths(x, 1)), (x)=>  Record.FromList({[Forecast] / a * Date.Day(x)}, {fn_Date(x)}) ),
              c = Record.Combine(b)
            ][c], type record ),
        CombinedR = [Promo ID = Text.Combine(_Detail[Promo ID], ";")] & Record.RemoveFields(_Detail{0}, {"Promo ID", "Forecast", "Start", "End"}) & AllDates & Record.Combine(Ad_R[R]),
        ToTbl = Table.FromRecords({CombinedR}),
        ChangedType = [ a = Table.ColumnNames(ToTbl),
                      b = List.Intersect({a, Table.ColumnNames(ChangedTypeSK)}),
                      c = Value.Type(Table.SelectColumns(Table.FirstN(ChangedTypeSK, 0), b) & Table.SelectColumns(Table.FirstN(ToTbl, 0), Record.FieldNames(AllDates))),
                      d = Value.ReplaceType(ToTbl, c),
                      e = Table.TransformColumnTypes(d, List.Transform(Record.FieldNames(AllDates), (x)=> {x, type number}))
                    ][e]
      ][ChangedType], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

Check this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lcvBDYAwDAPAXfquIjtJW1iFqvuvQQpCwIMHnziWfL0nqpWU02ZxVhCMXGjOOh8BEEkBRaFzSRfoWUY+fP307fZ+eYtSXr49vf7xYwc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Promo ID" = _t, #"Promo Type" = _t, #"Store ID" = _t, #"Product ID" = _t, Forecast = _t, Start = _t, End = _t]),
    ChangedTypeEN = Table.TransformColumnTypes(Source,{{"Forecast", type number}}, "en-US"),
    ChangedTypeSK = Table.TransformColumnTypes(ChangedTypeEN,{{"Start", type date}, {"End", type date}}, "sk-SK"),
    fn_Date = each Date.ToText(_, [Format="MMMyyyy", Culture = "en-US"]),
    AllDates = [ a = List.Buffer(List.Generate(
        ()=> Date.StartOfMonth(List.Min(ChangedTypeSK[Start])), 
        each _ <= Date.StartOfMonth(List.Max(ChangedTypeSK[End])),
        each Date.AddMonths(_, 1),
        each fn_Date(_) ) ),
    b = Record.FromList(List.Repeat({null}, List.Count(a)), a )
  ][b],
    StepBack = ChangedTypeSK,
    GroupedRows = Table.Group(StepBack, {"Promo Type", "Store ID", "Product ID"}, {{"All", each _}, {"T", each
      [ // _Detail = GroupedRows{[#"Promo Type"="Z3",#"Store ID"="90101",#"Product ID"="813416"]}[All],
        _Detail = _,
        Ad_R = Table.AddColumn(_Detail, "R", each 
            [ a = Duration.Days([End]-[Start])+1,
              b = List.Generate( ()=> let y = Date.EndOfMonth([Start]) in if y = Date.EndOfMonth([End]) then [End] else #date(Date.Year([Start]), Date.Month([Start]), Duration.Days(y - [Start])+1) ,
                                 (x)=> x <= Date.EndOfMonth([End]),
                                 (x)=> let y = Date.EndOfMonth(Date.AddMonths(x, 1)) in if y = Date.EndOfMonth([End]) then [End] else y,
                                 (x)=> Record.FromList({[Forecast] / a * Date.Day(x)}, {fn_Date(x)}) ),
              c = Record.Combine(b)
            ][c], type record ),
        CombinedR = [Promo ID = Text.Combine(_Detail[Promo ID], ";")] & Record.RemoveFields(_Detail{0}, {"Promo ID", "Forecast", "Start", "End"}) & AllDates & Record.Combine(Ad_R[R]),
        ToTbl = Table.FromRecords({CombinedR}),
        ChangedType = [ a = Table.ColumnNames(ToTbl),
                      b = List.Intersect({a, Table.ColumnNames(ChangedTypeSK)}),
                      c = Value.Type(Table.SelectColumns(Table.FirstN(ChangedTypeSK, 0), b) & Table.SelectColumns(Table.FirstN(ToTbl, 0), Record.FieldNames(AllDates))),
                      d = Value.ReplaceType(ToTbl, c),
                      e = Table.TransformColumnTypes(d, List.Transform(Record.FieldNames(AllDates), (x)=> {x, type number}))
                    ][e]
      ][ChangedType], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT