Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to Solution.
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.
Proud to be a Super User! | |
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
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
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
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
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.
Proud to be a Super User! | |