PowerPivot/PowerQuery: new Column and row context

[SOQLee]

Working in Excel 2013 PowerPivot and PowerQuery. In the first sample table below, the '0' needs to be replaced with the Account value in the previous row if the IDs are the same. Something similar to the Excel IF statement, ie. =IF(B2=B1,IF(C2=0,C1,C2),C2) using DAX or M. The second sample table is what I would like.

[TABLE="width: 202"]
<tbody>[TR]
[TD]Row[/TD]
[TD]ID[/TD]
[TD]Account[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]33333[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]C[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]D[/TD]
[TD]55555[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]D[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]D[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]E[/TD]
[TD]77777[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]F[/TD]
[TD]88888[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]F[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]F[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]G[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]G[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]G[/TD]
[TD]99999[/TD]
[/TR]
</tbody>[/TABLE]


This is what I would like to see:

[TABLE="width: 191"]
<tbody>[TR]
[TD]Row[/TD]
[TD]ID[/TD]
[TD]Account[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]33333[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD]33333[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]33333[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]33333[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B[/TD]
[TD]33333[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]C[/TD]
[TD]44444[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]D[/TD]
[TD]55555[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]D[/TD]
[TD]55555[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]D[/TD]
[TD]55555[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]E[/TD]
[TD]66666[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]E[/TD]
[TD]77777[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]F[/TD]
[TD]88888[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]F[/TD]
[TD]88888[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]F[/TD]
[TD]88888[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]G[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]G[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]G[/TD]
[TD]99999[/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

In the Query editor right click the Account header and select Fill>Down.

 

[SOQLee]

Unfortunately, that won't work for Rows 30, 31

 

[Norie]

Actually, you'll need an extra step - you'll first have to replace the 0s with Null.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,0,null,Replacer.ReplaceValue,{"Account"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Account"})
in
#"Filled Down"


Here's the result.


Row ID Account
1 A 11111
2 A 11111
3 A 22222
4 A 22222
5 B 33333
6 B 33333
7 B 33333
8 B 33333
9 B 33333
10 C 44444
11 C 44444
12 C 44444
13 C 44444
14 C 44444
15 C 44444
16 C 44444
17 C 44444
18 C 44444
19 D 55555
20 D 55555
21 D 55555
22 E 66666
23 E 66666
24 E 66666
25 E 66666
26 E 66666
27 E 77777
28 F 88888
29 F 88888
30 F 88888
31 G 88888
32 G 99999
33 G 99999

 

[krrishkrsna]

in Excel > place cursor on Table > menu Power Query > click on From Table > in Power Query Editor > on left side of window > right click on Table which is imported > click Duplicate > select duplicate table > remove column Row > select column Account > uncheck 0 > remove Duplicates > select column ID > remove Duplicates > click on Close & Load To > select Only Create Connection > click OK


menu Power Query > click Merge > select original table name and then select duplcated table name > select column ID for both tables > select Left Outer > click OK


you got the result but ID A having account 2222 will not appear. add some extra letter to A to get that also