Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
Hope you are doing amazingly well.
I have a stock out (fact) table (table 1) that shows the product , stock out start date, stock out end date, stock out value (among other attributes such as production site, impacted market, etc).
Each row represents an individual stock out (SO) event.
I want to be able to show the impact broken down in monthly time buckets. For that, I need to calculate :
I have already completed these 4 steps with the help of power queries.
What I would need help with is the below 3 points.
Any help would really be appreciated.
Table1
Product | Stock out Start Date | Stock out End Date | Stock out Value |
Product1 | 01/Jun/2022 | 30/Jun/2022 | 50 |
Product2 | 15/Aug/2022 | 15/Oct/2022 | 250 |
Product3 | 20/Nov/2022 | 10/Jan/2023 | 300 |
Table2
Product | Stock out Start Date | Stock out End Date | Stock out Duration | Stock out Value | Daily Value | Jun/2022 | Jul/2022 | Aug/2022 | Sep/2022 | Oct/2022 | Nov/2022 | Dec/2022 | Jan/2023 |
Product1 | 01/Jun/2022 | 30/Jun/2022 | 30 | 50 | 1.67 | 50 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Product2 | 15/Aug/2022 | 15/Oct/2022 | 62 | 250 | 4.03 | 0 | 0 | 68.55 | 120.97 | 60.48 | 0 | 0 | 0 |
Product3 | 20/Nov/2022 | 10/Jan/2023 | 51 | 300 | 5.77 | 0 | 0 | 0 | 0 | 0 | 63.46 | 178.85 | 57.69 |
Thank you in advance,
George
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLjFU0lEy1PUqzdM1MgIyjQ0QbFMDpVgduEKQiKGprmNpOkQWyPZPLoGwjVCVGoOEDHT98sugSoGGJgINNQZbAFQaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Stock out Start Date" = _t, #"Stock out End Date" = _t, #"Stock out Value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Stock out Start Date", type date}, {"Stock out End Date", type date}, {"Stock out Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Stock out Duration", each Duration.Days([Stock out End Date]-[Stock out Start Date])+1),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Daily Value", each Number.Round([Stock out Value]/[Stock out Duration],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Transform(List.Dates([Stock out Start Date],[Stock out Duration],#duration(1,0,0,0)),each Date.ToText(_,"MMM/yyyy"))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Grouped Rows" = Table.Group(#"Expanded Custom", {"Product", "Stock out Start Date", "Stock out End Date", "Stock out Value", "Stock out Duration", "Daily Value", "Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Total", each Number.Round([Count]*[Stock out Value]/[Stock out Duration],2)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Count"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Total", List.Sum)
in
#"Pivoted Column"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLjFU0lEy1PUqzdM1MgIyjQ0QbFMDpVgduEKQiKGprmNpOkQWyPZPLoGwjVCVGoOEDHT98sugSoGGJgINNQZbAFQaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Stock out Start Date" = _t, #"Stock out End Date" = _t, #"Stock out Value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Stock out Start Date", type date}, {"Stock out End Date", type date}, {"Stock out Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Stock out Duration", each Duration.Days([Stock out End Date]-[Stock out Start Date])+1),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Daily Value", each Number.Round([Stock out Value]/[Stock out Duration],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Transform(List.Dates([Stock out Start Date],[Stock out Duration],#duration(1,0,0,0)),each Date.ToText(_,"MMM/yyyy"))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Grouped Rows" = Table.Group(#"Expanded Custom", {"Product", "Stock out Start Date", "Stock out End Date", "Stock out Value", "Stock out Duration", "Daily Value", "Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Total", each Number.Round([Count]*[Stock out Value]/[Stock out Duration],2)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Count"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Total", List.Sum)
in
#"Pivoted Column"
Hello @Vijay_A_Verma
Thank you for your reply.
After copying your code, the result is the desired one, however, when I try to modify the code based on my real table, things don't work that well.
The real table can be seen below.
The real table already contains the stock out duration and also the daily stock out value, so a part of your code can be removed.
I believe that the issue I faced while working on your code had to do with the added custom 2 step onwards.
Can I please ask you to send me your revised code based on the real fact table?
Extract_Date | Week | BU | AREA | Event Destination Location | Destination_Country | Event Source Location | Primary Site | Primary Site Description | Event ID | Event Type Short Code | Event Product | Event Product Description | Event Root Cause | Event Predicted Start Date | Event Planned End Date | Event Duration | Event Lost Quantity | System - NSP | Event Sales Value (£) | Daily Sales Impact | HUB_Supply_Planning_Manager | HUB_Supply_Planner | HUB_Receiving_Planner | Brand | Category | Brand_Segmentation | Planning_HUB | Receiving_HUB | Responsibility | TradeRoute_Chain | Status_Chain | Destination_Market_Segmentation | Source Area | Flag |
17/06/2022 | 25 | BU1 | AREA1 | Event Destination Location1 | Destination_Country1 | Event Source Location1 | Primary Site1 | Primary Site Description1 | Event ID1 | Event Type Short Code1 | Event Product1 | Event Product Description1 | Event Root Cause1 | 10/08/2022 | 08/02/2023 | 182 | 496 | 6.00 | 2,976 | 16.35 | HUB_Supply_Planning_Manager1 | HUB_Supply_Planner1 | HUB_Receiving_Planner1 | Brand1 | Category1 | Brand_Segmentation1 | Planning_HUB1 | Receiving_HUB1 | Responsibility1 | TradeRoute_Chain1 | Status_Chain1 | Destination_Market_Segmentation1 | Source Area1 | Flag1 |
17/06/2022 | 25 | BU2 | AREA2 | Event Destination Location2 | Destination_Country2 | Event Source Location2 | Primary Site2 | Primary Site Description2 | Event ID2 | Event Type Short Code2 | Event Product2 | Event Product Description2 | Event Root Cause2 | 01/01/2022 | 01/05/2022 | 120 | 362 | 2.00 | 724 | 6.03 | HUB_Supply_Planning_Manager2 | HUB_Supply_Planner2 | HUB_Receiving_Planner2 | Brand2 | Category2 | Brand_Segmentation2 | Planning_HUB2 | Receiving_HUB2 | Responsibility2 | TradeRoute_Chain2 | Status_Chain2 | Destination_Market_Segmentation2 | Source Area2 | Flag2 |
17/06/2022 | 25 | BU3 | AREA3 | Event Destination Location3 | Destination_Country3 | Event Source Location3 | Primary Site3 | Primary Site Description3 | Event ID3 | Event Type Short Code3 | Event Product3 | Event Product Description3 | Event Root Cause3 | 24/06/2022 | 13/07/2022 | 19 | 165 | 2.00 | 330 | 17.37 | HUB_Supply_Planning_Manager3 | HUB_Supply_Planner3 | HUB_Receiving_Planner3 | Brand3 | Category3 | Brand_Segmentation3 | Planning_HUB3 | Receiving_HUB3 | Responsibility3 | TradeRoute_Chain3 | Status_Chain3 | Destination_Market_Segmentation3 | Source Area3 | Flag3 |
17/06/2022 | 25 | BU4 | AREA4 | Event Destination Location4 | Destination_Country4 | Event Source Location4 | Primary Site4 | Primary Site Description4 | Event ID4 | Event Type Short Code4 | Event Product4 | Event Product Description4 | Event Root Cause4 | 10/08/2022 | 31/08/2022 | 21 | 245 | 10.00 | 2,450 | 116.67 | HUB_Supply_Planning_Manager4 | HUB_Supply_Planner4 | HUB_Receiving_Planner4 | Brand4 | Category4 | Brand_Segmentation4 | Planning_HUB4 | Receiving_HUB4 | Responsibility4 | TradeRoute_Chain4 | Status_Chain4 | Destination_Market_Segmentation4 | Source Area4 | Flag4 |
17/06/2022 | 25 | BU5 | AREA5 | Event Destination Location5 | Destination_Country5 | Event Source Location5 | Primary Site5 | Primary Site Description5 | Event ID5 | Event Type Short Code5 | Event Product5 | Event Product Description5 | Event Root Cause5 | 21/09/2022 | 12/10/2022 | 21 | 394 | 3.00 | 1,182 | 56.29 | HUB_Supply_Planning_Manager5 | HUB_Supply_Planner5 | HUB_Receiving_Planner5 | Brand5 | Category5 | Brand_Segmentation5 | Planning_HUB5 | Receiving_HUB5 | Responsibility5 | TradeRoute_Chain5 | Status_Chain5 | Destination_Market_Segmentation5 | Source Area5 | Flag5 |
17/06/2022 | 25 | BU6 | AREA6 | Event Destination Location6 | Destination_Country6 | Event Source Location6 | Primary Site6 | Primary Site Description6 | Event ID6 | Event Type Short Code6 | Event Product6 | Event Product Description6 | Event Root Cause6 | 10/08/2022 | 14/09/2022 | 35 | 297 | 10.00 | 2,970 | 84.86 | HUB_Supply_Planning_Manager6 | HUB_Supply_Planner6 | HUB_Receiving_Planner6 | Brand6 | Category6 | Brand_Segmentation6 | Planning_HUB6 | Receiving_HUB6 | Responsibility6 | TradeRoute_Chain6 | Status_Chain6 | Destination_Market_Segmentation6 | Source Area6 | Flag6 |
17/06/2022 | 25 | BU7 | AREA7 | Event Destination Location7 | Destination_Country7 | Event Source Location7 | Primary Site7 | Primary Site Description7 | Event ID7 | Event Type Short Code7 | Event Product7 | Event Product Description7 | Event Root Cause7 | 24/06/2022 | 30/06/2022 | 6 | 409 | 6.00 | 2,454 | 409.00 | HUB_Supply_Planning_Manager7 | HUB_Supply_Planner7 | HUB_Receiving_Planner7 | Brand7 | Category7 | Brand_Segmentation7 | Planning_HUB7 | Receiving_HUB7 | Responsibility7 | TradeRoute_Chain7 | Status_Chain7 | Destination_Market_Segmentation7 | Source Area7 | Flag7 |
17/06/2022 | 25 | BU8 | AREA8 | Event Destination Location8 | Destination_Country8 | Event Source Location8 | Primary Site8 | Primary Site Description8 | Event ID8 | Event Type Short Code8 | Event Product8 | Event Product Description8 | Event Root Cause8 | 10/08/2022 | 05/10/2022 | 56 | 379 | 10.00 | 3,790 | 67.68 | HUB_Supply_Planning_Manager8 | HUB_Supply_Planner8 | HUB_Receiving_Planner8 | Brand8 | Category8 | Brand_Segmentation8 | Planning_HUB8 | Receiving_HUB8 | Responsibility8 | TradeRoute_Chain8 | Status_Chain8 | Destination_Market_Segmentation8 | Source Area8 | Flag8 |
17/06/2022 | 25 | BU9 | AREA9 | Event Destination Location9 | Destination_Country9 | Event Source Location9 | Primary Site9 | Primary Site Description9 | Event ID9 | Event Type Short Code9 | Event Product9 | Event Product Description9 | Event Root Cause9 | 10/08/2022 | 14/09/2022 | 35 | 238 | 4.00 | 952 | 27.20 | HUB_Supply_Planning_Manager9 | HUB_Supply_Planner9 | HUB_Receiving_Planner9 | Brand9 | Category9 | Brand_Segmentation9 | Planning_HUB9 | Receiving_HUB9 | Responsibility9 | TradeRoute_Chain9 | Status_Chain9 | Destination_Market_Segmentation9 | Source Area9 | Flag9 |
17/06/2022 | 25 | BU10 | AREA10 | Event Destination Location10 | Destination_Country10 | Event Source Location10 | Primary Site10 | Primary Site Description10 | Event ID10 | Event Type Short Code10 | Event Product10 | Event Product Description10 | Event Root Cause10 | 14/02/2022 | 30/10/2022 | 258 | 289 | 6.00 | 1,734 | 6.72 | HUB_Supply_Planning_Manager10 | HUB_Supply_Planner10 | HUB_Receiving_Planner10 | Brand10 | Category10 | Brand_Segmentation10 | Planning_HUB10 | Receiving_HUB10 | Responsibility10 | TradeRoute_Chain10 | Status_Chain10 | Destination_Market_Segmentation10 | Source Area10 | Flag10 |
17/06/2022 | 25 | BU11 | AREA11 | Event Destination Location11 | Destination_Country11 | Event Source Location11 | Primary Site11 | Primary Site Description11 | Event ID11 | Event Type Short Code11 | Event Product11 | Event Product Description11 | Event Root Cause11 | 21/09/2022 | 16/11/2022 | 56 | 246 | 10.00 | 2,460 | 43.93 | HUB_Supply_Planning_Manager11 | HUB_Supply_Planner11 | HUB_Receiving_Planner11 | Brand11 | Category11 | Brand_Segmentation11 | Planning_HUB11 | Receiving_HUB11 | Responsibility11 | TradeRoute_Chain11 | Status_Chain11 | Destination_Market_Segmentation11 | Source Area11 | Flag11 |
17/06/2022 | 25 | BU12 | AREA12 | Event Destination Location12 | Destination_Country12 | Event Source Location12 | Primary Site12 | Primary Site Description12 | Event ID12 | Event Type Short Code12 | Event Product12 | Event Product Description12 | Event Root Cause12 | 10/08/2022 | 31/08/2022 | 21 | 224 | 8.00 | 1,792 | 85.33 | HUB_Supply_Planning_Manager12 | HUB_Supply_Planner12 | HUB_Receiving_Planner12 | Brand12 | Category12 | Brand_Segmentation12 | Planning_HUB12 | Receiving_HUB12 | Responsibility12 | TradeRoute_Chain12 | Status_Chain12 | Destination_Market_Segmentation12 | Source Area12 | Flag12 |
17/06/2022 | 25 | BU13 | AREA13 | Event Destination Location13 | Destination_Country13 | Event Source Location13 | Primary Site13 | Primary Site Description13 | Event ID13 | Event Type Short Code13 | Event Product13 | Event Product Description13 | Event Root Cause13 | 10/08/2022 | 24/08/2022 | 14 | 129 | 6.00 | 774 | 55.29 | HUB_Supply_Planning_Manager13 | HUB_Supply_Planner13 | HUB_Receiving_Planner13 | Brand13 | Category13 | Brand_Segmentation13 | Planning_HUB13 | Receiving_HUB13 | Responsibility13 | TradeRoute_Chain13 | Status_Chain13 | Destination_Market_Segmentation13 | Source Area13 | Flag13 |
17/06/2022 | 25 | BU14 | AREA14 | Event Destination Location14 | Destination_Country14 | Event Source Location14 | Primary Site14 | Primary Site Description14 | Event ID14 | Event Type Short Code14 | Event Product14 | Event Product Description14 | Event Root Cause14 | 24/06/2022 | 23/11/2022 | 152 | 257 | 9.00 | 2,313 | 15.22 | HUB_Supply_Planning_Manager14 | HUB_Supply_Planner14 | HUB_Receiving_Planner14 | Brand14 | Category14 | Brand_Segmentation14 | Planning_HUB14 | Receiving_HUB14 | Responsibility14 | TradeRoute_Chain14 | Status_Chain14 | Destination_Market_Segmentation14 | Source Area14 | Flag14 |
17/06/2022 | 25 | BU15 | AREA15 | Event Destination Location15 | Destination_Country15 | Event Source Location15 | Primary Site15 | Primary Site Description15 | Event ID15 | Event Type Short Code15 | Event Product15 | Event Product Description15 | Event Root Cause15 | 10/08/2022 | 07/09/2022 | 28 | 130 | 4.00 | 520 | 18.57 | HUB_Supply_Planning_Manager15 | HUB_Supply_Planner15 | HUB_Receiving_Planner15 | Brand15 | Category15 | Brand_Segmentation15 | Planning_HUB15 | Receiving_HUB15 | Responsibility15 | TradeRoute_Chain15 | Status_Chain15 | Destination_Market_Segmentation15 | Source Area15 | Flag15 |
17/06/2022 | 25 | BU16 | AREA16 | Event Destination Location16 | Destination_Country16 | Event Source Location16 | Primary Site16 | Primary Site Description16 | Event ID16 | Event Type Short Code16 | Event Product16 | Event Product Description16 | Event Root Cause16 | 21/09/2022 | 19/10/2022 | 28 | 389 | 7.00 | 2,723 | 97.25 | HUB_Supply_Planning_Manager16 | HUB_Supply_Planner16 | HUB_Receiving_Planner16 | Brand16 | Category16 | Brand_Segmentation16 | Planning_HUB16 | Receiving_HUB16 | Responsibility16 | TradeRoute_Chain16 | Status_Chain16 | Destination_Market_Segmentation16 | Source Area16 | Flag16 |
17/06/2022 | 25 | BU17 | AREA17 | Event Destination Location17 | Destination_Country17 | Event Source Location17 | Primary Site17 | Primary Site Description17 | Event ID17 | Event Type Short Code17 | Event Product17 | Event Product Description17 | Event Root Cause17 | 21/09/2022 | 05/10/2022 | 14 | 215 | 9.00 | 1,935 | 138.21 | HUB_Supply_Planning_Manager17 | HUB_Supply_Planner17 | HUB_Receiving_Planner17 | Brand17 | Category17 | Brand_Segmentation17 | Planning_HUB17 | Receiving_HUB17 | Responsibility17 | TradeRoute_Chain17 | Status_Chain17 | Destination_Market_Segmentation17 | Source Area17 | Flag17 |
17/06/2022 | 25 | BU18 | AREA18 | Event Destination Location18 | Destination_Country18 | Event Source Location18 | Primary Site18 | Primary Site Description18 | Event ID18 | Event Type Short Code18 | Event Product18 | Event Product Description18 | Event Root Cause18 | 21/09/2022 | 12/10/2022 | 21 | 429 | 10.00 | 4,290 | 204.29 | HUB_Supply_Planning_Manager18 | HUB_Supply_Planner18 | HUB_Receiving_Planner18 | Brand18 | Category18 | Brand_Segmentation18 | Planning_HUB18 | Receiving_HUB18 | Responsibility18 | TradeRoute_Chain18 | Status_Chain18 | Destination_Market_Segmentation18 | Source Area18 | Flag18 |
17/06/2022 | 25 | BU19 | AREA19 | Event Destination Location19 | Destination_Country19 | Event Source Location19 | Primary Site19 | Primary Site Description19 | Event ID19 | Event Type Short Code19 | Event Product19 | Event Product Description19 | Event Root Cause19 | 21/09/2022 | 12/10/2022 | 21 | 327 | 6.00 | 1,962 | 93.43 | HUB_Supply_Planning_Manager19 | HUB_Supply_Planner19 | HUB_Receiving_Planner19 | Brand19 | Category19 | Brand_Segmentation19 | Planning_HUB19 | Receiving_HUB19 | Responsibility19 | TradeRoute_Chain19 | Status_Chain19 | Destination_Market_Segmentation19 | Source Area19 | Flag19 |
17/06/2022 | 25 | BU20 | AREA20 | Event Destination Location20 | Destination_Country20 | Event Source Location20 | Primary Site20 | Primary Site Description20 | Event ID20 | Event Type Short Code20 | Event Product20 | Event Product Description20 | Event Root Cause20 | 07/12/2022 | 21/12/2022 | 14 | 452 | 9.00 | 4,068 | 290.57 | HUB_Supply_Planning_Manager20 | HUB_Supply_Planner20 | HUB_Receiving_Planner20 | Brand20 | Category20 | Brand_Segmentation20 | Planning_HUB20 | Receiving_HUB20 | Responsibility20 | TradeRoute_Chain20 | Status_Chain20 | Destination_Market_Segmentation20 | Source Area20 | Flag20 |
Thank you,
George
Hello @Vijay_A_Verma ,
I think I am now able to modify your code to meet my real file's requirements.
Before flagging your reply as the solution, can you please explain why you have to use "each" at your code? From the moment you create a new colum, I would expect the code to be processed at each row, hence I don't understand why your code works only if you use "each".
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Transform(List.Dates([Stock out Start Date],[Stock out Duration],#duration(1,0,0,0)),each Date.ToText(_,"MMM/yyyy"))),
Thank you in advance,
George
For Table.AddColumn - each means this operation should be done for all cells one by one.
For List.Transform - each means this operation should be done for all elements one by one