Partial sales data / How to combine to full year forecast?

[Zdzislaw]

Hello to everyone. 

I'm looking a method to append sales scenarios into one table. My data contain product id and quantity with visibility to single month. Not each forecast from sales is considered official and on regular basis only partial information is provided.

For instance first forecast this year was covering full 12 months.

Second one contained only January updates on the month closure. 

February will be provided with updates Feb-Dec, as January is already fixed with actual data from previous forecast.

And so on...

Product id may change over the time.

I'm looking for navigation towards solution.

Thanks!

[Vera_33 · Accepted solution]

Hi @Zdzislaw 

Did you copy paste the code or modified by yourself? The error indicated [Index] not found, it worked in my file, below is my original code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7NCsIwEIRfpeTcg2uj0aNaCgr+gMeSQ01DL9KW2Aq+vZO4KcXDN2Rnd3ZTlqLonDXVa0iOtUjFzXX1aFDkKE5VCy3sA3quHHTXu/D++O7YBn16f2ygd9tDr2aAXro3NLdG6LQU5GfABiiwZRR7S7DitwQU6pjcsyv/EkQcowW7NO2P2QNIGBlH5ewLnjXI4kI1ZXNuE5PxXTk/G/PqZ2j9BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}, {"Forecast Id", type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", second, third}),
    #"Replaced Value" = Table.ReplaceValue(#"Appended Query",null,"no value",Replacer.ReplaceValue,{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Product ID", "Forecast Id"}, "Month", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Product ID", "Month"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [s1= Table.AddIndexColumn([allrows],"Index",0,1),
s2=Table.AddColumn(s1, "new", each List.Accumulate({0..[Index]},[Value], (x,y)=> 
[a=s1{y}[Value],
b= if x="no value" then  (try s1{[Index]-1}[Value] otherwise a) else x,
c=if b="no value" then  (try s1{y-1}[Value] otherwise a) else b][c]))][s2]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Forecast Id", "Product ID", "Month", "new"}, {"Forecast Id", "Product ID", "Month", "new"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Month]), "Month", "new")
in
    #"Pivoted Column"

[Jimmy801]

Hello @Zdzislaw 

if I understood you right you have a compete forecast of one year and you need to include in this forecast the monthly forecasts created monthly?

If yes you can use this approach

- create query with yearly forecast

- create query for every month

- create a join between the yearly forecast and jannuary forecast with a left-anti join (excluding jannuaries data from the yearly one

- Combine the reduced yearly forecast with the jannuary forecast.

- the last two steps you have to repeat for every month (but you need the use the output of the combined forecast for your new join - let's february)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Zdzislaw]

Indeed it will be much more efficient to explain you how I see and how I would like to see the data. Let me present a simplified input content:

Initial forecast:

Forecast Id	Product ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	A	8	7	9	9	7	8	2	5	8	4	1	5
1	B	4	4	7	8	2	11	5	10	8	1	7	9
1	C			4	10	4	9	9	9	6	3	11	7
1	D	9	1	1	3	2	4	5	10	9	6	7	10

A second forecast is limited to Feb only:

Forecast Id	Product ID	Feb
2	A	1
2	B	1
2	C
2	D	1

Third forecast revise Mar-Dec and bring new Product:

Forecast Id	Product ID	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	A	1	1	7	8	2	5	8	4	1	5
3	B	7	8	2	6	5	2	8	1	7	9
3	C	4	7	4	9	9	9	6	3	6	7
3	D
3	E	1	3	2	4	5	1	9	6	7	7

My expectation would be after Forecast #2 to work with following consolidation:

Forecast Id	Product ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	A	8	7	9	9	7	8	2	5	8	4	1	5
1	B	4	4	7	8	2	11	5	10	8	1	7	9
1	C			4	10	4	9	9	9	6	3	11	7
1	D	9	1	1	3	2	4	5	10	9	6	7	10
2	A	8	1	9	9	7	8	2	5	8	4	1	5
2	B	4	1	7	8	2	11	5	10	8	1	7	9
2	C			4	10	4	9	9	9	6	3	11	7
2	D	9	1	1	3	2	4	5	10	9	6	7	10

After Forecast #3 I would like to see: 

Forecast Id	Product ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	A	8	7	9	9	7	8	2	5	8	4	1	5
1	B	4	4	7	8	2	11	5	10	8	1	7	9
1	C			4	10	4	9	9	9	6	3	11	7
1	D	9	1	1	3	2	4	5	10	9	6	7	10
2	A	8	1	9	9	7	8	2	5	8	4	1	5
2	B	4	1	7	8	2	11	5	10	8	1	7	9
2	C			4	10	4	9	9	9	6	3	11	7
2	D	9	1	1	3	2	4	5	10	9	6	7	10
3	A	8	1	9	9	7	8	2	5	8	4	1	5
3	B	4	1	7	8	2	11	5	10	8	1	7	9
3	C			4	10	4	9	9	9	6	3	11	7
3	D	9	1
3	E			1	3	2	4	5	1	9	6	7	7

Greetings!

[Jimmy801]

Hello @Zdzislaw 

you can use my approach then. But to make that work you need to normalize your tables before and to bring your columns of months to rows like this. Now you can create a left anti join with your monthly forecast (using column product and attribute) and join them afterwards again. you have also to rename the forecast ID.  At the end join all 3 forecasts to one table

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Zdzislaw]

Hello Again,

In this case month filtering and appending new data set over excluded works well. 

I'm still seeking a direction to more automated solution that would push me to learn something new.

Z.

[Jimmy801]

Hello @Zdzislaw 

what you mean by more automated?

For sure there are more advanced way to let's say include like 10 forecast IDS at once. But if you only need to integrate some of them it's probably not worth by thinking about a more automated procedure.

BR

Jimmy

[Zdzislaw]

Jimmy,

That is a right way however still one thing to be solved. In consolidation after load of third forecast I would expect to not see Product Id "D" staring on March. When a specific product is eliminated then is just not shown in Forecast.

[Jimmy801]

Hello @Zdzislaw 

in your new forecast you need to put 0 when you want to overwrite the old value and let empty when you don't want to overwrite. You could use a filter before populating, where you filter out the 0-values.

BR

Jimmy

[Vera_33]

Hi @Zdzislaw 

Can you provide some sample data and the aiming result in a format we can copy (not a picture)?

[Zdzislaw]

Hi Vera, 

Just posted data layout and expected result.

Thank you

Zdzislaw

[Vera_33]

Hi @Zdzislaw 

Unpivot the table and append, add a custom column. This is the first table, I have another second, third (tables) which are the same as your sample (used in the append step). It is just one way, List.Accumulate, and I might make it too complicated, but it is learning, too:)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7NCsIwEIRfpeTcg2uj0aNaCgr+gMeSQ01DL9KW2Aq+vZO4KcXDN2Rnd3ZTlqLonDXVa0iOtUjFzXX1aFDkKE5VCy3sA3quHHTXu/D++O7YBn16f2ygd9tDr2aAXro3NLdG6LQU5GfABiiwZRR7S7DitwQU6pjcsyv/EkQcowW7NO2P2QNIGBlH5ewLnjXI4kI1ZXNuE5PxXTk/G/PqZ2j9BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}, {"Forecast Id", type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", second, third}),
    #"Replaced Value" = Table.ReplaceValue(#"Appended Query",null,0,Replacer.ReplaceValue,{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Product ID", "Forecast Id"}, "Month", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Product ID", "Month"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
    [s1= Table.AddIndexColumn([allrows],"Index",0,1),
    s2=Table.AddColumn(s1, "new", each List.Accumulate({0..[Index]},0, (state, current)=>
                                                                                [a=s1{current}[Value],
                                                                                b=if state = 0 then state + (if a=0 then (try s1{[Index]-1}[Value] otherwise 0) else a) else state][b]))][s2]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Forecast Id", "Product ID", "Month", "new"}, {"Forecast Id", "Product ID", "Month", "new"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Month]), "Month", "new")
in
    #"Pivoted Column"

[Zdzislaw]

Vera,

Indeed this is a method I would consider as the right one. 

For some reasons I've been reciveing an error while loading complete code from you - on the line where you referenced "second" and "third" source. I did the corrections to enable taht on my end with following code:

let
    Source = Table.Combine({Table1, Table2, Table3}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Forecast Id", "Product ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Product ID", "Month"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        [s1= Table.AddIndexColumn([allrows],"Index",0,1),
        s2=Table.AddColumn(s1, "new", each List.Accumulate({0..[Index]},0, (state, current)=>
        [a=s1{current}[Value],
        b=if state = 0 then state + (if a=0 then (try s1{[Index]-1}[Value] otherwise 0) else a) else state][b]))][s2]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Forecast Id", "Product ID", "Month", "new"}, {"Forecast Id", "Product ID", "Month", "new"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Month]), "Month", "new", List.Sum)
in
    #"Pivoted Column"

Unfortunately I do not receive forecast replaced by new one where required. Could you please take a look? 

[Vera_33]

Hi @Zdzislaw 

I have this step to replace null to 0, have a try. Besides, change the name in Unpivot step so you don't need to add another step:)

let
    Source = Table.Combine({Table1, Table2, Table3}),
    #"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Forecast Id", "Product ID"}, "Month", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Product ID", "Month"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        [s1= Table.AddIndexColumn([allrows],"Index",0,1),
        s2=Table.AddColumn(s1, "new", each List.Accumulate({0..[Index]},0, (state, current)=>
        [a=s1{current}[Value],
        b=if state = 0 then state + (if a=0 then (try s1{[Index]-1}[Value] otherwise 0) else a) else state][b]))][s2]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Forecast Id", "Product ID", "Month", "new"}, {"Forecast Id", "Product ID", "Month", "new"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Month]), "Month", "new")
in
    #"Pivoted Column"

[Zdzislaw]

Vera, 

I need to understand well List.Accumulate but for now I see that new forecast does not move to "new" column when expected. 

My other concern is - what if provided forecast will have 0 (zero) on one cell, will this cell be also replaced by previously stored value? In such situation we impact forecast by adding a value which is not correct.  

Thank you

Zdzislaw

[Vera_33]

Hi @Zdzislaw 

Yes, I found my mistake, and also considered the Zero, so I modified the code a little bit. List.Accumulate is like loop, do..while, you customize a function and it can call itself. Have a try:

let
    Source = Table.Combine({Table1, Table2, Table3}),
    #"Replaced Value" = Table.ReplaceValue(Source,null,"no value",Replacer.ReplaceValue,{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Forecast Id", "Product ID"}, "Month", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Product ID", "Month"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        [s1= Table.AddIndexColumn([allrows],"Index",0,1),
        s2=Table.AddColumn(s1, "new", List.Accumulate({0..[Index]},[Value], (x,y)=> 
[a=s1{y}[Value],
b= if x="no value" then  (try s1{[Index]-1}[Value] otherwise a) else x,
c=if b="no value" then  (try s1{y-1}[Value] otherwise a) else b][c]))][s2]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Forecast Id", "Product ID", "Month", "new"}, {"Forecast Id", "Product ID", "Month", "new"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Month]), "Month", "new")
in
    #"Pivoted Column"

[Zdzislaw]

Hi

I encounter following error message on Added Custom line:

Expression.Error: The field 'Index' of the record wasn't found.

[Vera_33 · Accepted solution]

Hi @Zdzislaw 

Did you copy paste the code or modified by yourself? The error indicated [Index] not found, it worked in my file, below is my original code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7NCsIwEIRfpeTcg2uj0aNaCgr+gMeSQ01DL9KW2Aq+vZO4KcXDN2Rnd3ZTlqLonDXVa0iOtUjFzXX1aFDkKE5VCy3sA3quHHTXu/D++O7YBn16f2ygd9tDr2aAXro3NLdG6LQU5GfABiiwZRR7S7DitwQU6pjcsyv/EkQcowW7NO2P2QNIGBlH5ewLnjXI4kI1ZXNuE5PxXTk/G/PqZ2j9BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}, {"Forecast Id", type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", second, third}),
    #"Replaced Value" = Table.ReplaceValue(#"Appended Query",null,"no value",Replacer.ReplaceValue,{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Product ID", "Forecast Id"}, "Month", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Product ID", "Month"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [s1= Table.AddIndexColumn([allrows],"Index",0,1),
s2=Table.AddColumn(s1, "new", each List.Accumulate({0..[Index]},[Value], (x,y)=> 
[a=s1{y}[Value],
b= if x="no value" then  (try s1{[Index]-1}[Value] otherwise a) else x,
c=if b="no value" then  (try s1{y-1}[Value] otherwise a) else b][c]))][s2]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Forecast Id", "Product ID", "Month", "new"}, {"Forecast Id", "Product ID", "Month", "new"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Month]), "Month", "new")
in
    #"Pivoted Column"

[Zdzislaw · Accepted solution]

Vera,

You've made my day. I would like to hug you. This is working well under different conditions I've simulated. 

Thank you!