The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
How can I remove the all the rows for the company within the day if error code is =1?
Regards,
Gian
Data | |||
Company | Total | Error Code | |
10/1/2024 | A | 8 | 0 |
10/1/2024 | A | 4 | 1 |
10/1/2024 | B | 6 | 0 |
10/1/2024 | B | 5 | 0 |
10/1/2024 | C | 8 | 0 |
10/1/2024 | C | 9 | 1 |
10/1/2024 | D | 10 | 0 |
10/1/2024 | D | 8 | 0 |
10/2/2024 | A | 7 | 0 |
10/2/2024 | A | 5 | 0 |
10/2/2024 | B | 9 | 0 |
10/2/2024 | B | 2 | 1 |
10/2/2024 | C | 0 | 0 |
10/2/2024 | C | 3 | 1 |
10/2/2024 | D | 2 | 0 |
10/2/2024 | D | 9 | 0 |
Result | |||
Company | Total | Error Code | |
10/1/2024 | B | 6 | 0 |
10/1/2024 | B | 5 | 0 |
10/1/2024 | D | 10 | 0 |
10/1/2024 | D | 8 | 0 |
10/2/2024 | A | 7 | 0 |
10/2/2024 | A | 5 | 0 |
10/2/2024 | D | 2 | 0 |
10/2/2024 | D | 9 | 0 |
Solved! Go to Solution.
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
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
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
Thanks Albert & AlienSx.
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
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
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