Merge Rows and total 2 categories

    • New

    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.

    • New

    Data changed to spreadsheets. Basically, need to merge rows by Invoice Line and sum both the Tax Rate and Tax Collected columns; leaving the other columns untouched. Also, this is a basic spreadsheet with only 9 rows. The solution would need to work for spreadsheets with hundreds to thousands of rows. Any help would be appreciated. Thank you.

    • New

    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 :)

    • New

    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 :)

    • New

    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

    :)

    • New

    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 :)

    • New

    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 :)

Participate now!

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

Register Yourself Login