Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

dates calculation and transpose / pivot table

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 :

 

  1. the duration of the SO event
  2. the daily impact
  3. count number of days between SO start date and end of the same month , and calculate SO impact for this period
  4. count number of days between SO end date and start of the same month, and calculate SO impact for this period

I have already completed these 4 steps with the help of power queries.

 

What I would need help with is the below 3 points.

 

  1. identify the full months between SO start month and SO end month
  2. calculate their duration and then the impact per month
  3. Transform my fact table as per below table 2 , where I will have additonal columns, first column being the earliest SO start month, last column being the latest SO end month and all columns in between being all the remaining months.

 

Any help would really be appreciated.

 

 

 

Table1

ProductStock out Start DateStock out End DateStock out Value
Product101/Jun/202230/Jun/202250
Product215/Aug/202215/Oct/2022250
Product320/Nov/202210/Jan/2023300

 

Table2

ProductStock out Start DateStock out End DateStock out DurationStock out ValueDaily ValueJun/2022Jul/2022Aug/2022Sep/2022Oct/2022Nov/2022Dec/2022Jan/2023

Product1

01/Jun/202230/Jun/202230501.67500000000
Product215/Aug/202215/Oct/2022622504.030068.55120.9760.48000
Product320/Nov/202210/Jan/2023513005.770000063.46

178.85

57.69

 

Thank you in advance,

 

George

 

 

 

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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_DateWeekBUAREAEvent Destination LocationDestination_CountryEvent Source LocationPrimary SitePrimary Site DescriptionEvent IDEvent Type Short CodeEvent ProductEvent Product DescriptionEvent Root CauseEvent Predicted Start DateEvent Planned End DateEvent DurationEvent Lost QuantitySystem - NSPEvent Sales Value (£)Daily Sales ImpactHUB_Supply_Planning_ManagerHUB_Supply_PlannerHUB_Receiving_PlannerBrandCategoryBrand_SegmentationPlanning_HUBReceiving_HUBResponsibilityTradeRoute_ChainStatus_ChainDestination_Market_SegmentationSource AreaFlag
      17/06/202225BU1AREA1Event Destination Location1Destination_Country1Event Source Location1Primary Site1Primary Site Description1Event ID1Event Type Short Code1Event Product1Event Product Description1Event Root Cause110/08/202208/02/20231824966.00                           2,97616.35HUB_Supply_Planning_Manager1HUB_Supply_Planner1HUB_Receiving_Planner1Brand1Category1Brand_Segmentation1Planning_HUB1Receiving_HUB1Responsibility1TradeRoute_Chain1Status_Chain1Destination_Market_Segmentation1Source Area1Flag1
      17/06/202225BU2AREA2Event Destination Location2Destination_Country2Event Source Location2Primary Site2Primary Site Description2Event ID2Event Type Short Code2Event Product2Event Product Description2Event Root Cause201/01/202201/05/20221203622.00                              7246.03HUB_Supply_Planning_Manager2HUB_Supply_Planner2HUB_Receiving_Planner2Brand2Category2Brand_Segmentation2Planning_HUB2Receiving_HUB2Responsibility2TradeRoute_Chain2Status_Chain2Destination_Market_Segmentation2Source Area2Flag2
      17/06/202225BU3AREA3Event Destination Location3Destination_Country3Event Source Location3Primary Site3Primary Site Description3Event ID3Event Type Short Code3Event Product3Event Product Description3Event Root Cause324/06/202213/07/2022191652.00                              33017.37HUB_Supply_Planning_Manager3HUB_Supply_Planner3HUB_Receiving_Planner3Brand3Category3Brand_Segmentation3Planning_HUB3Receiving_HUB3Responsibility3TradeRoute_Chain3Status_Chain3Destination_Market_Segmentation3Source Area3Flag3
      17/06/202225BU4AREA4Event Destination Location4Destination_Country4Event Source Location4Primary Site4Primary Site Description4Event ID4Event Type Short Code4Event Product4Event Product Description4Event Root Cause410/08/202231/08/20222124510.00                           2,450116.67HUB_Supply_Planning_Manager4HUB_Supply_Planner4HUB_Receiving_Planner4Brand4Category4Brand_Segmentation4Planning_HUB4Receiving_HUB4Responsibility4TradeRoute_Chain4Status_Chain4Destination_Market_Segmentation4Source Area4Flag4
      17/06/202225BU5AREA5Event Destination Location5Destination_Country5Event Source Location5Primary Site5Primary Site Description5Event ID5Event Type Short Code5Event Product5Event Product Description5Event Root Cause521/09/202212/10/2022213943.00                           1,18256.29HUB_Supply_Planning_Manager5HUB_Supply_Planner5HUB_Receiving_Planner5Brand5Category5Brand_Segmentation5Planning_HUB5Receiving_HUB5Responsibility5TradeRoute_Chain5Status_Chain5Destination_Market_Segmentation5Source Area5Flag5
      17/06/202225BU6AREA6Event Destination Location6Destination_Country6Event Source Location6Primary Site6Primary Site Description6Event ID6Event Type Short Code6Event Product6Event Product Description6Event Root Cause610/08/202214/09/20223529710.00                           2,97084.86HUB_Supply_Planning_Manager6HUB_Supply_Planner6HUB_Receiving_Planner6Brand6Category6Brand_Segmentation6Planning_HUB6Receiving_HUB6Responsibility6TradeRoute_Chain6Status_Chain6Destination_Market_Segmentation6Source Area6Flag6
      17/06/202225BU7AREA7Event Destination Location7Destination_Country7Event Source Location7Primary Site7Primary Site Description7Event ID7Event Type Short Code7Event Product7Event Product Description7Event Root Cause724/06/202230/06/202264096.00                           2,454409.00HUB_Supply_Planning_Manager7HUB_Supply_Planner7HUB_Receiving_Planner7Brand7Category7Brand_Segmentation7Planning_HUB7Receiving_HUB7Responsibility7TradeRoute_Chain7Status_Chain7Destination_Market_Segmentation7Source Area7Flag7
      17/06/202225BU8AREA8Event Destination Location8Destination_Country8Event Source Location8Primary Site8Primary Site Description8Event ID8Event Type Short Code8Event Product8Event Product Description8Event Root Cause810/08/202205/10/20225637910.00                           3,79067.68HUB_Supply_Planning_Manager8HUB_Supply_Planner8HUB_Receiving_Planner8Brand8Category8Brand_Segmentation8Planning_HUB8Receiving_HUB8Responsibility8TradeRoute_Chain8Status_Chain8Destination_Market_Segmentation8Source Area8Flag8
      17/06/202225BU9AREA9Event Destination Location9Destination_Country9Event Source Location9Primary Site9Primary Site Description9Event ID9Event Type Short Code9Event Product9Event Product Description9Event Root Cause910/08/202214/09/2022352384.00                              95227.20HUB_Supply_Planning_Manager9HUB_Supply_Planner9HUB_Receiving_Planner9Brand9Category9Brand_Segmentation9Planning_HUB9Receiving_HUB9Responsibility9TradeRoute_Chain9Status_Chain9Destination_Market_Segmentation9Source Area9Flag9
      17/06/202225BU10AREA10Event Destination Location10Destination_Country10Event Source Location10Primary Site10Primary Site Description10Event ID10Event Type Short Code10Event Product10Event Product Description10Event Root Cause1014/02/202230/10/20222582896.00                           1,7346.72HUB_Supply_Planning_Manager10HUB_Supply_Planner10HUB_Receiving_Planner10Brand10Category10Brand_Segmentation10Planning_HUB10Receiving_HUB10Responsibility10TradeRoute_Chain10Status_Chain10Destination_Market_Segmentation10Source Area10Flag10
      17/06/202225BU11AREA11Event Destination Location11Destination_Country11Event Source Location11Primary Site11Primary Site Description11Event ID11Event Type Short Code11Event Product11Event Product Description11Event Root Cause1121/09/202216/11/20225624610.00                           2,46043.93HUB_Supply_Planning_Manager11HUB_Supply_Planner11HUB_Receiving_Planner11Brand11Category11Brand_Segmentation11Planning_HUB11Receiving_HUB11Responsibility11TradeRoute_Chain11Status_Chain11Destination_Market_Segmentation11Source Area11Flag11
      17/06/202225BU12AREA12Event Destination Location12Destination_Country12Event Source Location12Primary Site12Primary Site Description12Event ID12Event Type Short Code12Event Product12Event Product Description12Event Root Cause1210/08/202231/08/2022212248.00                           1,79285.33HUB_Supply_Planning_Manager12HUB_Supply_Planner12HUB_Receiving_Planner12Brand12Category12Brand_Segmentation12Planning_HUB12Receiving_HUB12Responsibility12TradeRoute_Chain12Status_Chain12Destination_Market_Segmentation12Source Area12Flag12
      17/06/202225BU13AREA13Event Destination Location13Destination_Country13Event Source Location13Primary Site13Primary Site Description13Event ID13Event Type Short Code13Event Product13Event Product Description13Event Root Cause1310/08/202224/08/2022141296.00                              77455.29HUB_Supply_Planning_Manager13HUB_Supply_Planner13HUB_Receiving_Planner13Brand13Category13Brand_Segmentation13Planning_HUB13Receiving_HUB13Responsibility13TradeRoute_Chain13Status_Chain13Destination_Market_Segmentation13Source Area13Flag13
      17/06/202225BU14AREA14Event Destination Location14Destination_Country14Event Source Location14Primary Site14Primary Site Description14Event ID14Event Type Short Code14Event Product14Event Product Description14Event Root Cause1424/06/202223/11/20221522579.00                           2,31315.22HUB_Supply_Planning_Manager14HUB_Supply_Planner14HUB_Receiving_Planner14Brand14Category14Brand_Segmentation14Planning_HUB14Receiving_HUB14Responsibility14TradeRoute_Chain14Status_Chain14Destination_Market_Segmentation14Source Area14Flag14
      17/06/202225BU15AREA15Event Destination Location15Destination_Country15Event Source Location15Primary Site15Primary Site Description15Event ID15Event Type Short Code15Event Product15Event Product Description15Event Root Cause1510/08/202207/09/2022281304.00                              52018.57HUB_Supply_Planning_Manager15HUB_Supply_Planner15HUB_Receiving_Planner15Brand15Category15Brand_Segmentation15Planning_HUB15Receiving_HUB15Responsibility15TradeRoute_Chain15Status_Chain15Destination_Market_Segmentation15Source Area15Flag15
      17/06/202225BU16AREA16Event Destination Location16Destination_Country16Event Source Location16Primary Site16Primary Site Description16Event ID16Event Type Short Code16Event Product16Event Product Description16Event Root Cause1621/09/202219/10/2022283897.00                           2,72397.25HUB_Supply_Planning_Manager16HUB_Supply_Planner16HUB_Receiving_Planner16Brand16Category16Brand_Segmentation16Planning_HUB16Receiving_HUB16Responsibility16TradeRoute_Chain16Status_Chain16Destination_Market_Segmentation16Source Area16Flag16
      17/06/202225BU17AREA17Event Destination Location17Destination_Country17Event Source Location17Primary Site17Primary Site Description17Event ID17Event Type Short Code17Event Product17Event Product Description17Event Root Cause1721/09/202205/10/2022142159.00                           1,935138.21HUB_Supply_Planning_Manager17HUB_Supply_Planner17HUB_Receiving_Planner17Brand17Category17Brand_Segmentation17Planning_HUB17Receiving_HUB17Responsibility17TradeRoute_Chain17Status_Chain17Destination_Market_Segmentation17Source Area17Flag17
      17/06/202225BU18AREA18Event Destination Location18Destination_Country18Event Source Location18Primary Site18Primary Site Description18Event ID18Event Type Short Code18Event Product18Event Product Description18Event Root Cause1821/09/202212/10/20222142910.00                           4,290204.29HUB_Supply_Planning_Manager18HUB_Supply_Planner18HUB_Receiving_Planner18Brand18Category18Brand_Segmentation18Planning_HUB18Receiving_HUB18Responsibility18TradeRoute_Chain18Status_Chain18Destination_Market_Segmentation18Source Area18Flag18
      17/06/202225BU19AREA19Event Destination Location19Destination_Country19Event Source Location19Primary Site19Primary Site Description19Event ID19Event Type Short Code19Event Product19Event Product Description19Event Root Cause1921/09/202212/10/2022213276.00                           1,96293.43HUB_Supply_Planning_Manager19HUB_Supply_Planner19HUB_Receiving_Planner19Brand19Category19Brand_Segmentation19Planning_HUB19Receiving_HUB19Responsibility19TradeRoute_Chain19Status_Chain19Destination_Market_Segmentation19Source Area19Flag19
      17/06/202225BU20AREA20Event Destination Location20Destination_Country20Event Source Location20Primary Site20Primary Site Description20Event ID20Event Type Short Code20Event Product20Event Product Description20Event Root Cause2007/12/202221/12/2022144529.00                           4,068290.57HUB_Supply_Planning_Manager20HUB_Supply_Planner20HUB_Receiving_Planner20Brand20Category20Brand_Segmentation20Planning_HUB20Receiving_HUB20Responsibility20TradeRoute_Chain20Status_Chain20Destination_Market_Segmentation20Source Area20Flag20

       

      Thank you,

       

      George

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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