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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jamuka
Helper IV
Helper IV

Divide Column Values to New Columns

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 IDPromo TypeStore IDProduct IDForecastStartEnd
1235Z3901018134168.0001.01.202528.02.2025
1236Z3901018134167.0001.04.202531.05.2025
1237Z3901028134167.0001.04.202531.05.2025

 

Expected Result

Promo IDPromo TypeStore IDProduct IDForecast2025 Jan2025 Feb2025 Mar2025 Apr2025 May
1235;1236Z3901018134168.0004.2033.797 3.4433.557
1237Z3901028134167.000   3.4433.557

 

kind regards

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3 ACCEPTED SOLUTIONS
jgeddes
Super User
Super User

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

jgeddes_1-1737481766375.png

to...

jgeddes_2-1737481784757.png

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
Super User
Super User

Hi @jamuka, another solution:

 

Output

dufoq3_0-1737580659545.png

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

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

9 REPLIES 9
jamuka
Helper IV
Helper IV

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 

StartEndJanuaryFebruary
1.01.2025  14.01.2025  3114

 

it shows 

StartEndJanuaryFebruary
1.01.2025  14.01.2025  31  28

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.

Dear @dufoq3,

 

thank you for your help.

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
Helper IV
Helper IV

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

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
Super User
Super User

Hi @jamuka, another solution:

 

Output

dufoq3_0-1737580659545.png

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
Helper IV
Helper IV

Dear @jgeddes 

 

thank you for your help.

 

jgeddes
Super User
Super User

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

jgeddes_1-1737481766375.png

to...

jgeddes_2-1737481784757.png

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!





Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors