Merge Rows and total 2 categories

    Photos are fairly useless as we cannot manipulate the data in them. Suggest you upload your data in a file so that we can test our solutions before offering them up to you. Additionally, we are all volunteers with limited time available to help. Don't make us retype your data and guess at your formatting. Help us to help you by making it easy for us to help.

    Hi,


    Ideal question for alansidman ;)


    For sure, Alan will come up with a brilliant Power Query solution to group by Invoice Line (Column H)

    and to sum only Columns AM and AN...


    Note a Pivot Table could also handle this question rather quickly

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi,


    To be reviewed by Alan ... ;)

    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Invoice Line"}, {{"Sum of Tax Collected", each List.Sum([Tax Collected]), type number}, {"Sum of Tax Rate", each List.Sum([Tax Rate]), type number}})
in
    #"Grouped Rows"

    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Mark,


    Should you need to retain All the Other 44 Columns for your Group By ...

    below is the modified M code :

    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Co Code","Billing Party","Invoice Number","Invoice Date","Customer Class","Customer Number","Customer Name","Invoice Line","TPC","TPC Name","Line Description","Prod Type","Item Name","Tax Category","Distr Method","DesignLive","GeoTool","OrderForm","RptUsage","Tax Result","Exempt Code","OBM Brand","Product Owner","SepStatedCat","PerformLoc","Sales Person","Linked Invoice","UseTax Reason","SourceMethod","ClientBill State","ClientHQ State","ShipTo Zip","ZipSellPrice","ZipQuantity","ShipTo State","ShipTo County","ShipTo City","Tax Base Amount","ShipFrom Plant","ShipFrom State","ShipFrom Zip","Interim Plant","Osprey OrderID","Osprey MailDropID","Osprey CustNo"}, {{"Sum of Tax Collected", each List.Sum([Tax Collected]), type number}, {"Sum of Tax Rate", each List.Sum([Tax Rate]), type number}})
in
    #"Grouped Rows"

    and attached is your Test file

    :)

    Once you have tested out the M code, feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    No such thing as a stupid question !!! ;)


    Indeed, unlike Excel, Power Query has its own "engine" and does not update itself automatically ...


    If the test file attached in post #8 (Test MarcoTex) does produce your expected result ... ( you have not answered this question .... :( )

    probably the easiest solution, for you, is to copy your entire database from your current file to this test file ... and, then,

    From Excel's main menu, just click on Data > Refresh All

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Here is how I would do it.

    1. Group the table on the Invoice Line and sum the two tax columns

    Code
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Date", type date}, {"Tax Collected", type number}, {"Tax Rate", Percentage.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice Line"}, {{"Sum of Tax Collected", each List.Sum([Tax Collected]), type nullable number}, {"Sum of Tax Rate", each List.Sum([Tax Rate]), type nullable number}})
in
    #"Grouped Rows"

    Then Merge (join) the new query with the original data (ie. Merge back on itself)


    Code
    let
    Source = Table.NestedJoin(Table1, {"Invoice Line"}, #"Table1 (2)", {"Invoice Line"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Co Code", "Billing Party", "Invoice Number", "Invoice Date", "Customer Class", "Customer Number", "Customer Name", "TPC", "TPC Name", "Line Description", "Prod Type", "Item Name", "Tax Category", "Distr Method", "DesignLive", "GeoTool", "OrderForm", "RptUsage", "Tax Result", "Exempt Code", "OBM Brand", "Product Owner", "SepStatedCat", "PerformLoc", "Sales Person", "Linked Invoice", "UseTax Reason", "SourceMethod", "ClientBill State", "ClientHQ State", "ShipTo Zip", "ZipSellPrice", "ZipQuantity", "ShipTo State", "ShipTo County", "ShipTo City", "Tax Base Amount", "ShipFrom Plant", "ShipFrom State", "ShipFrom Zip", "Interim Plant", "Osprey CustNo", "Osprey OrderID", "Osprey MailDropID"}, {"Table1 (2).Co Code", "Table1 (2).Billing Party", "Table1 (2).Invoice Number", "Table1 (2).Invoice Date", "Table1 (2).Customer Class", "Table1 (2).Customer Number", "Table1 (2).Customer Name", "Table1 (2).TPC", "Table1 (2).TPC Name", "Table1 (2).Line Description", "Table1 (2).Prod Type", "Table1 (2).Item Name", "Table1 (2).Tax Category", "Table1 (2).Distr Method", "Table1 (2).DesignLive", "Table1 (2).GeoTool", "Table1 (2).OrderForm", "Table1 (2).RptUsage", "Table1 (2).Tax Result", "Table1 (2).Exempt Code", "Table1 (2).OBM Brand", "Table1 (2).Product Owner", "Table1 (2).SepStatedCat", "Table1 (2).PerformLoc", "Table1 (2).Sales Person", "Table1 (2).Linked Invoice", "Table1 (2).UseTax Reason", "Table1 (2).SourceMethod", "Table1 (2).ClientBill State", "Table1 (2).ClientHQ State", "Table1 (2).ShipTo Zip", "Table1 (2).ZipSellPrice", "Table1 (2).ZipQuantity", "Table1 (2).ShipTo State", "Table1 (2).ShipTo County", "Table1 (2).ShipTo City", "Table1 (2).Tax Base Amount", "Table1 (2).ShipFrom Plant", "Table1 (2).ShipFrom State", "Table1 (2).ShipFrom Zip", "Table1 (2).Interim Plant", "Table1 (2).Osprey CustNo", "Table1 (2).Osprey OrderID", "Table1 (2).Osprey MailDropID"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Table1 (2)", {"Invoice Line"})
in
    #"Removed Duplicates"

    alansidman


    Thanks a lot for your feedback :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login