Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Power query group by/merge question

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power query group by/merge question

    ABCDEFGH
    1VendorOrder RefCreation DateTotal PO AmountPO CurrencyDocumentPayment AmountPayment Date
    21000010034B111111112/24/2018191.78USD5100006334203.841/17/2019
    31000010034B111111112/24/2018191.78USD00.00
    41000010034C11111112/4/2019400.00USD00.00
    5
    6Desired output:
    7
    8VendorOrder RefCreation DateTotal PO AmountPO CurrencyDocumentPayment AmountPayment Date
    91000010034B111111112/24/2018191.78USD5100006334203.841/17/2019
    101000010034C11111112/4/2019400.00USD00.00

    Sheet3 (2)





    How can I reduce the first table into the desired output using Power Query?
    Not sure how to deal with the zeroes.
    Line 3 is created when I book the invoice
    Line 2 is created when the invoice is paid
    So in essence, for that particular invoice B1111111, I only need to see Line 2 in the output

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

    Cool Re: Power query group by/merge question

    try

    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
    Result
    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


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"Order Ref", type text}, {"Creation Date", type date}, {"Total PO Amount", type number}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", type number}, {"Payment Date", type date}}),
        Group = Table.Group(Type, {"Vendor", "Order Ref", "Creation Date", "PO Currency"}, {{"Count", each _, type table}}),
        List1 = Table.AddColumn(Group, "Total PO Amount", each List.First(List.Distinct(Table.Column([Count],"Total PO Amount")))),
        List2 = Table.AddColumn(List1, "Document", each List.First(List.Distinct(Table.Column([Count],"Document")))),
        List3 = Table.AddColumn(List2, "Payment Amount", each List.First(List.Distinct(Table.Column([Count],"Payment Amount")))),
        List4 = Table.AddColumn(List3, "Payment Date", each List.First(List.Distinct(Table.Column([Count],"Payment Date")))),
        ROC = Table.SelectColumns(List4,{"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
        TypeDate = Table.TransformColumnTypes(ROC,{{"Payment Date", type date}})
    in
        TypeDate
    M-code is not optimised

    edit:
    btw. try to post your posts to the proper forum
    Last edited by sandy666; Jul 27th, 2019 at 10:39 AM.
    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

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power query group by/merge question

    Quote Originally Posted by sandy666 View Post

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"Order Ref", type text}, {"Creation Date", type date}, {"Total PO Amount", type number}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", type number}, {"Payment Date", type date}}),
        Group = Table.Group(Type, {"Vendor", "Order Ref", "Creation Date", "PO Currency"}, {{"Count", each _, type table}}),
        List1 = Table.AddColumn(Group, "Total PO Amount", each List.First(List.Distinct(Table.Column([Count],"Total PO Amount")))),
        List2 = Table.AddColumn(List1, "Document", each List.First(List.Distinct(Table.Column([Count],"Document")))),
        List3 = Table.AddColumn(List2, "Payment Amount", each List.First(List.Distinct(Table.Column([Count],"Payment Amount")))),
        List4 = Table.AddColumn(List3, "Payment Date", each List.First(List.Distinct(Table.Column([Count],"Payment Date")))),
        ROC = Table.SelectColumns(List4,{"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
        TypeDate = Table.TransformColumnTypes(ROC,{{"Payment Date", type date}})
    in
        TypeDate
    M-code is not optimised

    edit:
    btw. try to post your posts to the proper forum
    Ok will do, thanks Sandy.

    What do you mean by M-code is not optimised?

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

    Default Re: Power query group by/merge question

    Quote Originally Posted by danhendo888 View Post
    What do you mean by M-code is not optimised?
    because maybe there exist a much better solution, more sophisticated

    and please don't quote whole post, use Reply instead of Reply With Quote
    Last edited by sandy666; Jul 27th, 2019 at 08:54 PM.
    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

  5. #5
    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

    Sandy, maybe you would know if this is more or less optimized than your solution, but here is a different way to get the same results.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"Order Ref", type text}, {"Creation Date", type date}, {"Total PO Amount", type number}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", type number}, {"Payment Date", type date}}),
        Group = Table.Group(Type, {"Order Ref"}, {{"Custom", each Table.First(_), type table}}),
        List = Table.AddColumn(Group, "Custom.1", each Record.ToList([Custom])),
        Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
        Split = Table.SplitColumn(Extract, "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6", "Custom.1.7", "Custom.1.8"}),
        TypeII = Table.TransformColumnTypes(Split,{{"Custom.1.1", type number}, {"Custom.1.2", type text}, {"Custom.1.3", type date}, {"Custom.1.4", type number}, {"Custom.1.5", type text}, {"Custom.1.6", type number}, {"Custom.1.7", type number}, {"Custom.1.8", type date}}),
        Rename = Table.RenameColumns(TypeII,{{"Custom.1.1", "Vendor"}, {"Custom.1.3", "Creation Date"}, {"Custom.1.4", "Total PO Amount"}, {"Custom.1.5", "PO Currency"}, {"Custom.1.6", "Document"}, {"Custom.1.7", "Payment Amount"}, {"Custom.1.8", "Payment Date"}}),
        Remove = Table.RemoveColumns(Rename,{"Custom.1.2"}),
        Reorder = Table.ReorderColumns(Remove,{"Vendor", "Order Ref", "Custom", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"})
    in
        Reorder
    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.

  6. #6
    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

    Just found another way. I think this one is better than my last.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Group = Table.Group(Source, {"Order Ref"}, {{"Custom", each Table.First(_), type table}}),
        List = Group[Custom],
        ToTable = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}, {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
        Type = Table.TransformColumnTypes(Expand,{{"Creation Date", type date}, {"Payment Date", type date}, {"Total PO Amount", Currency.Type}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", Currency.Type}})
    in
        Type
    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.

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

    Default Re: Power query group by/merge question

    last one (post#6) is very nice
    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

  8. #8
    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

    Thanks, buddy!
    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.

  9. #9
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power query group by/merge question

    Quote Originally Posted by lrobbo314 View Post
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Group = Table.Group(Source, {"Order Ref"}, {{"Custom", each Table.First(_), type table}}),
        List = Group[Custom],
        ToTable = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}, {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
        Type = Table.TransformColumnTypes(Expand,{{"Creation Date", type date}, {"Payment Date", type date}, {"Total PO Amount", Currency.Type}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", Currency.Type}})
    in
        Type
    Hello friends,
    Can someone help me breakdown/read the following in the step called "Group":
    each Table.First(_), type table

    Thank you

  10. #10
    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

    It’s grouping everything together based on the ‘Order Ref’ field, and the table.first function limits the resets to the first record from each Order Ref value. If you click on each step in the power query editor, you can see what is happening to the data. I manually edited the code to include the table.first function. Not sure if it is possible to do purely through the GUI.
    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.

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
  •