none
Remove rows with zero value in Excel power query RRS feed

  • Question

  • Hi,

    This is my first participation in this forum and I hope you can assist me.

    I have a query with 3 columns and there are rows that have zero value across all the 3 rows, how can I delete these rows?

    Thanks

    Amr


    • Edited by AmrMatar Tuesday, October 22, 2019 6:30 PM Making the question clearer
    Tuesday, October 22, 2019 6:28 PM

Answers

  • Hi,

    As far as I understand you want to filter out rows, where Col1 = 0, Col2 = 0 and Col3 = 0. If it's the case, you may use this code:

    = Table.SelectRows(Source, each not List.MatchesAll({[Col1],[Col2],[Col3]}, each _ = 0))
    • Edited by Aleksei Zhigulin Wednesday, October 23, 2019 8:44 AM
    • Proposed as answer by Lz._ Wednesday, October 23, 2019 4:15 PM
    • Marked as answer by AmrMatar Thursday, October 24, 2019 9:54 AM
    Wednesday, October 23, 2019 8:40 AM
  • Hi Amr

    Assuming you want to do this after your existing #"Filtered Rows" step, use Aleksei's proposal changing [Col1],[Col2],[Col3] with YOUR column names:

    = Table.SelectRows(#"Filtered Rows", each not List.MatchesAll({[Rooms],[IN_GUEST],[REVENUE]}, each _ = 0))

    • Marked as answer by AmrMatar Thursday, October 24, 2019 9:54 AM
    Wednesday, October 23, 2019 11:31 AM
  • Amr

    1/ There MUST be a comma at the end of the #"Filtered Rows" step as it's not the last one

    2/ You MUST give a name to the step = Table.SelectRows(#"Filtered Rows", each not...) and reference it after the in line. So the end of your query code should look like this:

    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Rate Code] <> "{NULL}")),
    RemovedRowsWithZero = Table.SelectRows(#"Filtered Rows", each not List.MatchesAll({[Rooms],[IN_GUEST],[REVENUE]}, each _ = 0))
    
    in
        RemovedRowsWithZero

    • Marked as answer by AmrMatar Thursday, October 24, 2019 9:54 AM
    Wednesday, October 23, 2019 1:07 PM

All replies

  • I have a query with 3 columns and there are rows that have zero value across all the 3 rows (I guess you meant Columns), how can I delete these rows?

    Hi Amr

    With Table.RemoveMatchingRows:

    let
        Source = Table.FromRecords({[Col1=10, Col2=10, Col3=100],[Col1=0, Col2=0, Col3=0],[Col1=0, Col2=100, Col3=0],[Col1=0.1, Col2=0, Col3=0],[Col1=0, Col2=0, Col3=0]}),
        Remove = Table.RemoveMatchingRows(Source, {[Col1=0, Col2=0, Col3=0]})
    in
        Remove

    and if you have i.e. 30 columns, instead of writting/coding: {[Col1=0, Col2=0, Col3=0..., Col30=0]} you can do something like:

    let
        Source = Table.FromRecords({[Col1=10, Col2=10, Col3=100],[Col1=0, Col2=0, Col3=0],[Col1=0, Col2=100, Col3=0],[Col1=0.1, Col2=0, Col3=0],[Col1=0, Col2=0, Col3=0]}),
        ColumnNames = Table.ColumnNames(Source),
        Remove = Table.RemoveMatchingRows(Source,
            { Record.FromList(List.Repeat({0},List.Count(ColumnNames)), ColumnNames) }
        )
    in
        Remove



    • Edited by Lz._ Tuesday, October 22, 2019 9:24 PM Add. info
    Tuesday, October 22, 2019 8:56 PM
  • Hi,

    As far as I understand you want to filter out rows, where Col1 = 0, Col2 = 0 and Col3 = 0. If it's the case, you may use this code:

    = Table.SelectRows(Source, each not List.MatchesAll({[Col1],[Col2],[Col3]}, each _ = 0))
    • Edited by Aleksei Zhigulin Wednesday, October 23, 2019 8:44 AM
    • Proposed as answer by Lz._ Wednesday, October 23, 2019 4:15 PM
    • Marked as answer by AmrMatar Thursday, October 24, 2019 9:54 AM
    Wednesday, October 23, 2019 8:40 AM
  • Hi,

    Thank you for the prompt response. I have tried to apply this solution but it didn't work with me. Please forgive my poor knowledge as I'm just a starter in Power Query.

    For easier explanation, I have uploaded my sample file and all what I need is to remove rows with zero values in Columns C,D& E. 

    https://drive.google.com/file/d/1J8RQkQbbmGAGx9jB_rP6_u-uN2ayxnF7/view?usp=sharing 

    Appreciate your assistance.

    Regards

    Amr

    Wednesday, October 23, 2019 10:33 AM
  • Hi,

    Thank you for the prompt response. I have tried to apply this solution but it didn't work with me. Please forgive my poor knowledge as I'm just a starter in Power Query.

    For easier explanation, I have uploaded my sample file and all what I need is to remove rows with zero values in Columns C,D& E. 

    https://drive.google.com/file/d/1J8RQkQbbmGAGx9jB_rP6_u-uN2ayxnF7/view?usp=sharing 

    Appreciate your assistance.

    Regards

    Amr

    Wednesday, October 23, 2019 10:35 AM
  • Hi Amr

    Assuming you want to do this after your existing #"Filtered Rows" step, use Aleksei's proposal changing [Col1],[Col2],[Col3] with YOUR column names:

    = Table.SelectRows(#"Filtered Rows", each not List.MatchesAll({[Rooms],[IN_GUEST],[REVENUE]}, each _ = 0))

    • Marked as answer by AmrMatar Thursday, October 24, 2019 9:54 AM
    Wednesday, October 23, 2019 11:31 AM
  • Hi,

    Your assumption is right. I tried using Akleksei's  by even coping the formula as you typed, but it seems that there is an error "The name filtered rows is recognized". I'm sure it is me probably doing something wrong here. Sorry for my poor knowledge.

    If you managed to try this, would be grateful if you can send me the file. Appreciated

    Thanks

    Wednesday, October 23, 2019 12:40 PM
  • This is how I put it by the way:

    let
        Source = Folder.Files("C:\Users\amatar\OneDrive\Work\ADCC\OM\Project\Summaries\Data\LY"),
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
        #"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform File from LY", each #"Transform File from LY"([Content])),
        #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from LY"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from LY", Table.ColumnNames(#"Transform File from LY"(#"Sample File"))),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"RESORT", type text}, {"BUSINESS_DATE", type date}, {"CHAR_BUSINESS_DATE", type date}, {"MARKET_CODE", type text}, {"CF_MASTER_SEQ", type text}, {"GROUP_NAME", type text}, {"S_REV_MKT", type number}, {"S_DEF_ROOMS_MKT", Int64.Type}, {"S_FB_REV_MKT", Int64.Type}, {"S_MISC_REV_MKT", type number}, {"MKT_RESORT_ROOM", Int64.Type}, {"GUEST_MKT", Int64.Type}, {"TOT_ARR_MKT", type number}, {"PER_OCC_MKT", type number}, {"PER_DOUBLE_MKT", type number}, {"DOUBLE_OCC_MKT", Int64.Type}, {"SINGLE_OCC_MKT", Int64.Type}, {"GUEST_DAY", Int64.Type}, {"ARRIVAL_DAY", Int64.Type}, {"ARRIVAL_MKT", Int64.Type}, {"TOT_ARR_DAY", type number}, {"PER_OCC_DAY", type number}, {"PER_DOUBLE_OCC_DAY", type number}, {"SINGLE_OCC_DAY", Int64.Type}, {"S_MISC_REV_DAY", type number}, {"DAY_RESORT_ROOM", Int64.Type}, {"S_FB_REV_DAY", Int64.Type}, {"DOUBLE_OCC_DAY", Int64.Type}, {"S_REV_DAY", type number}, {"S_DEF_ROOMS_DAY", Int64.Type}, {"ARR_TODAY", Int64.Type}, {"NO_DEFINITE_ROOMS", Int64.Type}, {"IN_GUEST", Int64.Type}, {"OCC_SINGLE", Int64.Type}, {"DOUBLE_OCC", Int64.Type}, {"REVENUE", type number}, {"FB_REV", Int64.Type}, {"OTHER_REV", type number}, {"TOTAL_REVENUE", type number}, {"RESORT_ROOM", Int64.Type}, {"PER_OCC", type number}, {"GET_ARR", type number}, {"MULTI_OCC_PER", type number}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"RESORT"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"BUSINESS_DATE", "Date"}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"CHAR_BUSINESS_DATE"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"MARKET_CODE", "Rate Code"}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"CF_MASTER_SEQ", "GROUP_NAME", "S_REV_MKT", "S_DEF_ROOMS_MKT", "S_FB_REV_MKT", "S_MISC_REV_MKT", "MKT_RESORT_ROOM", "GUEST_MKT", "TOT_ARR_MKT", "PER_OCC_MKT", "PER_DOUBLE_MKT", "DOUBLE_OCC_MKT", "SINGLE_OCC_MKT", "GUEST_DAY", "ARRIVAL_DAY", "ARRIVAL_MKT", "TOT_ARR_DAY", "PER_OCC_DAY", "PER_DOUBLE_OCC_DAY", "SINGLE_OCC_DAY", "S_MISC_REV_DAY", "DAY_RESORT_ROOM", "S_FB_REV_DAY", "DOUBLE_OCC_DAY", "S_REV_DAY", "S_DEF_ROOMS_DAY", "ARR_TODAY"}),
        #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"NO_DEFINITE_ROOMS", "Rooms"}}),
        #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"OCC_SINGLE", "DOUBLE_OCC", "FB_REV", "OTHER_REV", "TOTAL_REVENUE", "RESORT_ROOM", "PER_OCC", "GET_ARR", "MULTI_OCC_PER"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns3",{{"REVENUE", Int64.Type}, {"IN_GUEST", Int64.Type}, {"Rooms", Int64.Type}}),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Date"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Rate Code] <> "{NULL}"))
    = Table.SelectRows(#"Filtered Rows", each not List.MatchesAll({[Rooms],[IN_GUEST],[REVENUE]}, each _ = 0))

    in
        #"Filtered Rows"

    Wednesday, October 23, 2019 12:41 PM
  • Amr

    1/ There MUST be a comma at the end of the #"Filtered Rows" step as it's not the last one

    2/ You MUST give a name to the step = Table.SelectRows(#"Filtered Rows", each not...) and reference it after the in line. So the end of your query code should look like this:

    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Rate Code] <> "{NULL}")),
    RemovedRowsWithZero = Table.SelectRows(#"Filtered Rows", each not List.MatchesAll({[Rooms],[IN_GUEST],[REVENUE]}, each _ = 0))
    
    in
        RemovedRowsWithZero

    • Marked as answer by AmrMatar Thursday, October 24, 2019 9:54 AM
    Wednesday, October 23, 2019 1:07 PM
  • Hi,

    Thank you so much for helping me out with that, it worked perfectly.

    Regards

    Thursday, October 24, 2019 9:55 AM