Forum Discussion

myriam_ouan's avatar
myriam_ouan
Regular Visitor
5 years ago
Solved

Somme.si.ens sur power query

Bonjour,   Je ne suis pas tres familière avec power query mais je souhaite savoir s’il est possible de faire la chose suivante :   J’ai un tableau qui regroupe des montants sur une période auxque...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Bonjour, myriam_ouan , tu peux l'atteindre par la procédure comme ça

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YlWMgKydU0RbEMkthFUDUi9LkwCxDEyVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KEY = _t, AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AMOUNT", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
        each
        [
            sum = List.Sum(Table.Group(#"Changed Type", {"KEY"}, {{"All", each _}}){[KEY=[KEY]]}[All][AMOUNT]),
            test = if sum>0 then [C1="c"] else [C2="b"]
        ][test]
    ),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"C1", "C2"}, {"C1", "C2"})
in
    #"Expanded Custom"

    mais, je suis pas sûr si on doit changer des fonctions en version fraincaise (e.g. if => si) car j'utilisais tourjours Power Query en version anglaise.

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi myriam_ouan 

Can you supply an image of your data and expected results?  I'm not clear what you mean by sign - it might be something confused in the French to English Google translation.

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

myriam_ouan's avatar
myriam_ouan
Regular Visitor
5 years ago

Hi Phil, thanks for your interest in my problem. 
basically I want to determine wether the sum by accounting key of my numbers is positive or negative in order to assign it a code. 

Here's a simplified example :

 

KEY       AMOUNT  C1   C2

1           100            a     b

2           -50            c     d

2           150          c     d

2           200          c     d

1           -150         a     b

1            25           a     b

 

If the sum of each amount corresponding to a key is positive it will be assigned the corresponding value in the column C1. If negative, C2. So with the example above, the sum for key 1 is negative so each amount will be assigned the code "b". For key 2 the sum is positive so each amount will be assigned the code "c". 


As you will notice, some numbers will be given the negative associated code despite being positive and vice versa. 

I hope this gives a better insight of my problem.

Thanks

 

Myriam

 

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Bonjour, myriam_ouan , tu peux l'atteindre par la procédure comme ça

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YlWMgKydU0RbEMkthFUDUi9LkwCxDEyVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KEY = _t, AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AMOUNT", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
        each
        [
            sum = List.Sum(Table.Group(#"Changed Type", {"KEY"}, {{"All", each _}}){[KEY=[KEY]]}[All][AMOUNT]),
            test = if sum>0 then [C1="c"] else [C2="b"]
        ][test]
    ),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"C1", "C2"}, {"C1", "C2"})
in
    #"Expanded Custom"

    mais, je suis pas sûr si on doit changer des fonctions en version fraincaise (e.g. if => si) car j'utilisais tourjours Power Query en version anglaise.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Bonjour CNENFRNL , Je dois regrouper plusieurs lignes pour n'en faire qu'une et pour ne pas perdre mes données, je dois calculer toutes les colunes qui contiennent de valeurs.  J'arrive à effectuer cette opération sur Excel en utilisant Somme.si.ens.  ... C'est trop compliqué lol