Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi community,
I want to delete rows based on few conditions. If fileds doc_numer , date , intance have same values and In field pay_doc_no any one or more row has value start with 2 or 1 then delete all rows from data as they have same doc_numer , date , intance and all condtion should true if not then we have to include rows in report.
I need to perform this transforamtion in power query . I attached my sample data and highlighed rows which needs to delete .please refer my comments filed.
Hi Avinash , Thanks for response .
this is not working for one condition . like below green highlighted documents. As I can see pay_doc_no is showing NA .so it should available in report for both date (16, 17).
hi @shalinik ,
create a blank query. Copy and paste the below code into the advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndG7CoMwFAbgVwmZBc1N7VikQ5dO3cTBmiMEbCw2QunTN+kl0nqhmOEny5f/hJPnmIaEhTSiDAf4sLXBI3eIvR0zl5ykNve6anoJuAiehH8IjV5nMyt3t2l5Xy0Xxpwj5F3GvBSDjNl/ZasJdRMQblO3BklowACS/aVRVWlAolNvUN21ZyRVXUMH2iClr6bU1eLjsZ+Hpi7V947EBEk9YSwZr1X8rlV4mQxlXIw/P1U2S4oH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, pay_doc_no = _t, doc_number = _t, Instance = _t, amt = _t, comments = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"pay_doc_no", type text}, {"doc_number", Int64.Type}, {"Instance", type text}, {"amt", Int64.Type}, {"comments", type text}}),
#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "UniqueValue", each Text.Combine({Text.From([doc_number], "en-CA"), Text.From([date], "en-CA"), [Instance]}, ""), type text),
#"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"UniqueValue"}, {{"Count", each _, type table [date=nullable date, pay_doc_no=nullable text, doc_number=nullable number, Instance=nullable text, amt=nullable number, comments=nullable text, Merged=text]}, {"RowCount", each Table.RowCount(_)= 1, Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([RowCount] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"UniqueValue", "RowCount"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"date", "pay_doc_no", "doc_number", "Instance", "amt", "comments"}, {"date", "pay_doc_no", "doc_number", "Instance", "amt", "comments"})
in
#"Expanded Count"
Steps:
1. Merged columns date, instance and doc number
2. Grouped by this merged column, ("Count") a column with a table of all rows and another column ("RowCount") to count rows.
3. Filter the row count column for TRUE.
4. Removed all columns except "Count"
5. Expanded count.
Output;
file:
Delete rows based on conditions.pbix
Hi Avinash,
I have another issue , if I have only one row and pay_doc_no satrt with 2 or 1 then we have to exclude row from report as document paid .
Thanks You..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |