none
Maintaining time phased 'Network Flow' data in a table (using Power Query)? RRS feed

  • Question

  • Hi,

    I've the following Excel table:

      A B C D E F
    1 Month Starting Inventory Beginning On Hand Inventory Incoming Supply Shipments Ending On Hand Inventory
    2  M1                           100                                              100                       100              50                                         150
    3  M2                              -                                                150                       100              75                                         175
    4  M3                              -                                                175                       100              88                                         188
    5  M4                              -                                                188                       100              94                                         194
    6  M5                              -                                                194                       100              97                                         197
    7  M6                              -                                                197                       100              98                                         198

    Columns A, B and D are given (data). Other columns are calculated. The formula in each of the columns is given below.
      A B C D E F
    1 Month Starting Inventory Beginning On Hand Inventory Incoming Supply Shipments Ending On Hand Inventory
    2 M1 100 =IF([@Month]="M1",[@[Starting Inventory]],F1) 100 =50%*[@[Beginning On Hand Inventory]] =[@[Beginning On Hand Inventory]]+[@[Incoming Supply]]-[@Shipments]
    3 M2 0 =IF([@Month]="M1",[@[Starting Inventory]],F2) 100 =50%*[@[Beginning On Hand Inventory]] =[@[Beginning On Hand Inventory]]+[@[Incoming Supply]]-[@Shipments]
    4 M3 0 =IF([@Month]="M1",[@[Starting Inventory]],F3) 100 =50%*[@[Beginning On Hand Inventory]] =[@[Beginning On Hand Inventory]]+[@[Incoming Supply]]-[@Shipments]
    5 M4 0 =IF([@Month]="M1",[@[Starting Inventory]],F4) 100 =50%*[@[Beginning On Hand Inventory]] =[@[Beginning On Hand Inventory]]+[@[Incoming Supply]]-[@Shipments]
    6 M5 0 =IF([@Month]="M1",[@[Starting Inventory]],F5) 100 =50%*[@[Beginning On Hand Inventory]] =[@[Beginning On Hand Inventory]]+[@[Incoming Supply]]-[@Shipments]
    7 M6 0 =IF([@Month]="M1",[@[Starting Inventory]],F6) 100 =50%*[@[Beginning On Hand Inventory]] =[@[Beginning On Hand Inventory]]+[@[Incoming Supply]]-[@Shipments]

    Given Columns A,B and D, can we create this table in Power Query?





    • Edited by nvPQ Friday, December 15, 2017 1:24 AM
    Friday, December 15, 2017 1:17 AM

Answers