Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Power query group by/merge question

  1. #11
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power query group by/merge question

    Quote Originally Posted by lrobbo314 View Post
    post#6
    I suggest to check your M-code for this source data:

    Vendor Order Ref Creation Date Total PO Amount PO Currency Document Payment Amount Payment Date
    1000010034
    B1111111
    24/12/2018
    191.78
    USD
    5100006334
    203.84
    17/01/2019
    1000010034
    B1111111
    24/12/2018
    191.78
    USD
    0
    0
    1000010034
    C1111111
    04/02/2019
    400
    USD
    0
    0
    1000010035
    D222
    01/05/2019
    222.55
    USD
    666667
    543.8
    01/06/2019
    1000010035
    D222
    01/05/2019
    222.55
    USD
    0
    0
    1000010035
    D222
    10/10/2019
    123
    USD
    0
    0


    result is:

    Vendor Order Ref Creation Date Total PO Amount PO Currency Document Payment Amount Payment Date
    1000010034
    B1111111
    24/12/2018
    191.78
    USD
    5100006334
    203.84
    17/01/2019
    1000010034
    C1111111
    04/02/2019
    400
    USD
    0
    0
    1000010035
    D222
    01/05/2019
    222.55
    USD
    666667
    543.8
    01/06/2019


    but IMHO should be:

    Vendor Order Ref Creation Date Total PO Amount PO Currency Document Payment Amount Payment Date
    1000010034
    B1111111
    24/12/2018
    191.78
    USD
    5100006334
    203.84
    17/01/2019
    1000010034
    C1111111
    04/02/2019
    400
    USD
    0
    0
    1000010035
    D222
    01/05/2019
    222.55
    USD
    666667
    543.8
    01/06/2019
    1000010035
    D222
    10/10/2019
    123
    USD
    0
    0
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  2. #12
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power query group by/merge question

    Yeah, the OP was confusing to me and I'm not really sure if my code is correct or not. It was too small of a sample data set and desired results.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #13
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,819
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power query group by/merge question

    your code works for single set but with more data (as you can see) it doesn't
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •