Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Maintain time phased 'Network Flow' database in a Power Query table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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?





    Friday, December 15, 2017 1:30 AM

Answers