Solved: Re: Divide Column Values to New Columns

jamuka · ‎01-21-2025

Dear all,

I'd want to unpivot below table and add new Month columns based on min Start column and max End column. Then add Forecast quantities to relative month column and after thet group rows Promo ID, Promo Type, Store ID and Product ID.

I found a similiar topic Solved: Re: Prorated annual expense - Microsoft Fabric Community where @AlienSx solved but I couldn't update formula to calculate duration days instead of hard coded 12 months.

My Current Table

Promo ID	Promo Type	Store ID	Product ID	Forecast	Start	End
1235	Z3	90101	813416	8.000	1.01.2025	28.02.2025
1236	Z3	90101	813416	7.000	1.04.2025	31.05.2025
1237	Z3	90102	813416	7.000	1.04.2025	31.05.2025

Expected Result

Promo ID	Promo Type	Store ID	Product ID	Forecast	2025 Jan	2025 Feb	2025 Mar	2025 Apr	2025 May
1235;1236	Z3	90101	813416	8.000	4.203	3.797		3.443	3.557
1237	Z3	90102	813416	7.000				3.443	3.557

kind regards

jgeddes · ‎01-21-2025

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

View solution in original post

dufoq3 · ‎01-22-2025

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

dufoq3 · ‎01-29-2025

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

jamuka · ‎01-29-2025

Dear @jgeddes, @dufoq3

the code uses first day of Month in Start column value and last day of Month in End column value.

instead of

Start	End	January	February
1.01.2025	14.01.2025	31	14

it shows

Start	End	January	February
1.01.2025	14.01.2025	31	28

dufoq3 · ‎01-29-2025

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

jamuka · ‎01-29-2025

Dear @dufoq3,

thank you for your help.

dufoq3 · ‎01-30-2025

You're welcome 😉

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

jamuka · ‎01-22-2025

Dear @dufoq3

thank you for help.

I tried to learn how to merge a step with another step, with your below code now I know how to do it.

CombinedR = [Promo ID = Text.Combine(_Detail[Promo ID], ";")] & Record.RemoveFields(_Detail{0}, {"Promo ID", "Forecast", "Start", "End"}) & AllDates & Record.Combine(Ad_R[R]),

have a nice day

dufoq3 · ‎01-22-2025

You're welcome. Enjoy 😉

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3 · ‎01-22-2025

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

jamuka · ‎01-21-2025

Dear @jgeddes

thank you for your help.

jgeddes · ‎01-21-2025

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Divide Column Values to New Columns

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - June 2025

Fabric Community Update - June 2025

Join the #PBI10 DataViz contest

Divide Column Values to New Columns

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - June 2025

Fabric Community Update - June 2025