Forum Discussion

MyOx's avatar
MyOx
Frequent Visitor
2 years ago
Solved

Divide row A by row B for all column

Hello,

 

I trying to build an attrition table in power query (not Dax).

I have two table containing all my topics: one with the amount the remaining items (A), one with the maximum item (B).

I want, for each column to do A/B.

Here's an exemple:

TopicRowDébutM1M2M3M4M5
Topic 1A100806040200
Topic 2A1008570554025
        
TopicRowDébutM1M2M3M4M5
Topic 1B10010090858075
Topic 2B10010080604020

 

The final table should look like this

TopicRow M1M2M3M4M5
Topic 1Final Result (A/B) 80%67%47%25%0%
Topic 2Final Result (A/B) 85%88%92%100%125%

 

Concidering that I have a thousand rows (Topic 1 to Topic 1000) and a thousand column (M1 to M1000), how could I do that?

 

Note: So far I found that if both Table are merge, I can do this = Table.ReplaceValue(#"Added Index", each [M2], each [M2]/#"Added Index"[M2]{[Index] -1}, Replacer.ReplaceValue,{"M2"}) to get my answer for one column. Not enough but it'

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Hello, MyOx 

    let
    tbl1 = your_table_one,
    tbl2 = your_table_two,
    topics = List.Buffer(tbl1[Topic]),
    names = List.Buffer(List.Skip(Table.ColumnNames(tbl1), 3)),
    rows_one = List.Buffer(Table.ToRows(Table.SelectColumns(tbl1, names))),
    rows_two = List.Buffer(Table.ToRows(Table.SelectColumns(tbl2, names))),
    positions = List.Positions(rows_one), 
    tx = 
        List.Transform(
            positions, 
            (x) => 
                [a = List.Zip({rows_one{x}, rows_two{x}}),
                b = {topics{x}} & List.Transform(a, each Value.Divide(_{0}, _{1}))][b]
        ),
    fnl = Table.FromRows(tx, {"Topic"} & names)
in
    fnl

1 Reply

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Hello, MyOx 

    let
    tbl1 = your_table_one,
    tbl2 = your_table_two,
    topics = List.Buffer(tbl1[Topic]),
    names = List.Buffer(List.Skip(Table.ColumnNames(tbl1), 3)),
    rows_one = List.Buffer(Table.ToRows(Table.SelectColumns(tbl1, names))),
    rows_two = List.Buffer(Table.ToRows(Table.SelectColumns(tbl2, names))),
    positions = List.Positions(rows_one), 
    tx = 
        List.Transform(
            positions, 
            (x) => 
                [a = List.Zip({rows_one{x}, rows_two{x}}),
                b = {topics{x}} & List.Transform(a, each Value.Divide(_{0}, _{1}))][b]
        ),
    fnl = Table.FromRows(tx, {"Topic"} & names)
in
    fnl