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,863
    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
    I don't use vba in any form!

  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,863
    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
    I don't use vba in any form!

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
  •