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

• ### 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 Friday, December 15, 2017 1:24 AM
Friday, December 15, 2017 1:17 AM