none
Power Query - Record Calculated from the above RRS feed

  • 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

Answers

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
    Moderator
  • 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
    Linked List.
    With unreal fictitious dataset.
    Easier with vintage Excel.
    http://www.mediafire.com/file/kdxxfb5au3ynrh6/08_13_19a.xlsx/file
    http://www.mediafire.com/file/hjstc2ypb7w8rad/08_13_19a.pdf/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