# Power Query - Record Calculated from the above • ### Question

• I have a table like this

ACTIVITY A; ACTIVITY B; %; Q

ACTIVITY 1; ACTIVITY 2; 95%; 95 (as activity 1 = 100)

ACTIVITY 2; ACTIVITY 3; 100%; 95 (95 from the above x 100%)

ACTIVITY 3; ACTIVITY 4; 80%; 76 (95 from the above x 80%)

ETC

Each record is a multiplication of % and a record above.

Please note that the record above is not always in the same place, this is why I can't use a reference, I have to use a search above.

How can I solve the issue?

Monday, August 12, 2019 3:50 PM

### All replies

• Hi and.alf,

I have problems to understand your request. How about posting a link to a file that contains representative sample data including the desired result?

Make sure to include multiple "calculation chains" so that one can spot the pattern.

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com

Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

Friday, August 16, 2019 6:25 PM
• In your real dataset, are the activities labeled ACTIVITY 1, ACTIVITY 2 etc.? If not, how would you know the order of the activities?
Friday, August 16, 2019 11:11 PM
• Excel 365 Pro Plus
With unreal fictitious dataset.
Easier with vintage Excel.
http://www.mediafire.com/file/kdxxfb5au3ynrh6/08_13_19a.xlsx/file

Sunday, August 18, 2019 1:41 AM
• ```let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Typed = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type number}}),
a = List.Buffer(Typed[A]),
b = List.Buffer(Typed[B]),
c = List.Buffer(Typed[C]),
fnNewRow = (x, prevC)=>
if x = -1
then null
else
[A = a{x},
B = b{x},
C = c{x},
D = C*prevC,
next = List.PositionOf(a, B)],
collect = List.Generate(
()=>fnNewRow(0, 1),
each _<> null,
each fnNewRow([next], [D]),
each [A = [A], B = [B], C = [C], D = [D]]
),
newtable = Table.FromRecords(collect, type table [A=text, B = text, C = Percentage.Type, D = Percentage.Type])
in
newtable```

Maxim Zelensky Excel Inside

Sunday, August 25, 2019 11:01 AM