Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
gianchan
Frequent Visitor

Remove rows based on error code

Hi all,

How can I remove the all the rows for the company within the day if error code is =1?

Regards,

Gian

 

 

Data   
 CompanyTotalError Code
10/1/2024A80
10/1/2024A41
10/1/2024B60
10/1/2024B50
10/1/2024C80
10/1/2024C91
10/1/2024D100
10/1/2024D80
10/2/2024A70
10/2/2024A50
10/2/2024B90
10/2/2024B21
10/2/2024C00
10/2/2024C31
10/2/2024D20
10/2/2024D90
    
Result   
 CompanyTotalError Code
10/1/2024B60
10/1/2024B50
10/1/2024D100
10/1/2024D80
10/2/2024A70
10/2/2024A50
10/2/2024D20
10/2/2024D90
2 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    result = Table.RemoveMatchingRows(
        Source, 
        Table.ToRecords(
            Table.SelectRows(Source, (x) => x[Error Code] = 1)[[Data], [Company]]
        ),
        {"Data", "Company"}
    )
in
    result

View solution in original post

Anonymous
Not applicable

Hi @gianchan ,
You can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5LCsAgDETvkrVgTGs/y2puId7/GjUgVOq4GAJ5TPJKocA+eGHZydHTcrUwVTcTm2EiqeWAHSMRkrz8Y+SGf9S2DEv6Pyej9rkkEZLUFTCRUU5GbYYdIxvsaL82d/QzqC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Company = _t, Total = _t, #"Error Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Company", type text}, {"Total", Int64.Type}, {"Error Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Company"}, {{"GroupedData", each _, type table [Date=nullable date, Company=nullable text, Total=nullable number, Error Code=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "HasErrorCode1", each if List.Contains([GroupedData][Error Code], 1) then 1 else 0),
    #"Expanded GroupedData" = Table.ExpandTableColumn(#"Added Custom", "GroupedData", {"Total", "Error Code"}, {"Total", "Error Code"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded GroupedData", each ([HasErrorCode1] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"HasErrorCode1"})
in
    #"Removed Columns"

Final output

vheqmsft_0-1727938436315.png

 

Best regards,
Albert He


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

3 REPLIES 3
gianchan
Frequent Visitor

Thanks Albert & AlienSx.

Anonymous
Not applicable

Hi @gianchan ,
You can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5LCsAgDETvkrVgTGs/y2puId7/GjUgVOq4GAJ5TPJKocA+eGHZydHTcrUwVTcTm2EiqeWAHSMRkrz8Y+SGf9S2DEv6Pyej9rkkEZLUFTCRUU5GbYYdIxvsaL82d/QzqC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Company = _t, Total = _t, #"Error Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Company", type text}, {"Total", Int64.Type}, {"Error Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Company"}, {{"GroupedData", each _, type table [Date=nullable date, Company=nullable text, Total=nullable number, Error Code=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "HasErrorCode1", each if List.Contains([GroupedData][Error Code], 1) then 1 else 0),
    #"Expanded GroupedData" = Table.ExpandTableColumn(#"Added Custom", "GroupedData", {"Total", "Error Code"}, {"Total", "Error Code"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded GroupedData", each ([HasErrorCode1] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"HasErrorCode1"})
in
    #"Removed Columns"

Final output

vheqmsft_0-1727938436315.png

 

Best regards,
Albert He


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

AlienSx
Super User
Super User

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    result = Table.RemoveMatchingRows(
        Source, 
        Table.ToRecords(
            Table.SelectRows(Source, (x) => x[Error Code] = 1)[[Data], [Company]]
        ),
        {"Data", "Company"}
    )
in
    result

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Kudoed Authors