Posts by alansidman

  • Merge Rows and total 2 categories

    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"

  • 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.

  • IGNORE - MISTAKE

    I have removed your attachment as it appears to contain actual confidential data. If the data is in fact not real, then repost the file, however, if the data was real, then repost your file with fictitious data

  • Get result with code replacing the formula

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.



    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.



    - Follow this link for an introduction to Power Query functionality.



    - Follow this link for a video which demonstrates how to use Power Query code provided.

  • Get result with code replacing the formula

    Here is a power query solution


    Code
    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1],"Total") then [Column1] else null),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Custom] <> null)),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","-Total","",Replacer.ReplaceText,{"Custom"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Custom"})
in
#"Removed Other Columns"

  • Formula to display numbers only

    An alternative is with Power Query


    Code
    let
Source = Excel.CurrentWorkbook(){[Name="Table_0"]}[Content],
#"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Column4", each Text.BeforeDelimiter(_, " "), type text}, {"Column5", each Text.BeforeDelimiter(_, " "), type text}, {"Column6", each Text.BeforeDelimiter(_, " "), type text}})
in
#"Extracted Text Before Delimiter"