• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove specific rows using the power query editor

DSP Varma

Member
Please help me out with a code on how to remove rows filled in yellow in the Power query editor (rows where the Fare, Tax, and Commission are zero)
 

Attachments

  • Sample Data - Remove rows.xlsx
    10.1 KB · Views: 3
Here is one possibility

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"product", type text}, {"Fare", Int64.Type}, {"tax", Int64.Type}, {"commission", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Fare]= 0 and [tax]= 0 and [commission]= 0 then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"
 
Thanks Alan.
 
In this case it's possible to do without any added column, as one can use a function inside the Table.SelectRows argument.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}, {"product", type text}, {"Fare", Int64.Type}, {"tax", Int64.Type}, {"commission", Int64.Type}}),
    Filter = Table.SelectRows(#"Changed Type", each List.Sum({[Fare],[tax],[commission]})>0)
in
    Filter

Or
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}, {"product", type text}, {"Fare", Int64.Type}, {"tax", Int64.Type}, {"commission", Int64.Type}}),
    Filter = Table.SelectRows(#"Changed Type", each not List.AllTrue({[Fare]=0,[tax]=0,[commission]=0}))
in
    Filter
 
Back
Top