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, I have the table like below in Power BI, I want to remove some rows based on condition. if the same row is added and removed just for a change of case those two records need to be removed.
Original Table
ID File Date Value IsValueAddition
------------------------------------------------------------------------
1 1.txt 6/8/2020 ABC true
2 2.txt 8/9/2020 PqR true
3 3.txt 8/10/2020 xYz true
4 2.txt 9/11/2020 PqR false
5 2.txt 9/11/2020 PQR true
6 1.txt 9/18/2020 ABC false
7 4.txt 10/2/2020 dEF true
Output Table after removals
ID File Date Value IsValueAddition
------------------------------------------------------------------------
2 2.txt 8/9/2020 PqR true
3 3.txt 8/10/2020 xYz true
7 4.txt 10/2/2020 dEF true
Notes:
1) The Row's 1 and 6 removed because the same content was added and the removed the content (Technically nothing written to file)
2) The Row's 4 and 5 removed because the two rows technically added just to change the case of the value.
Appreciate any help to solve this issue.
Solved! Go to Solution.
1. for each combination of file and Uppercase of Value, add 1 for true and subtract 1 for false - into a new aggregated table
2. if the result is 1, select the first row for that combination
3. Discard all other rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRAAK3zJxUBRTgkliCJgIEYYk5pciinsVgEceUlMySzPw8pVidaCVDsIyhXklFCYpeM30LfSMDIwMUQUcnZxR+SVFpKtgUIzDXCMMUC31LTFMCCoOwm2IM5hpjMcXQAMOYisgq7KaY4HCLpb6hIYYp6G5JS8wphhhjSpIxgTi8ZAbmYgYv0BTM8EUPXoRjzMF8EwxjQOGCYUyKqxsWx8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Positions" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 6, 24, 45, 61}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Positions", [PromoteAllScalars=true]),
#"Uppercased Text" = Table.TransformColumns(#"Promoted Headers",{{"Value ", Text.Upper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Uppercased Text","true","1",Replacer.ReplaceText,{"IsValueAddition"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","false","-1",Replacer.ReplaceText,{"IsValueAddition"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"IsValueAddition", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"File ", "Value "}, {{"Count", each List.Sum([IsValueAddition]), type nullable number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"File "}),
#"Merged Queries" = Table.NestedJoin(#"Promoted Headers", {"File "}, #"Removed Other Columns", {"File "}, "Removed Other Columns", JoinKind.Inner),
#"Removed Other Columns1" = Table.SelectColumns(#"Merged Queries",{"ID ", "File ", "Date ", "Value ", "IsValueAddition"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1", {"File "})
in
#"Removed Duplicates"
Something like this, just more elegant 🙂
1. for each combination of file and Uppercase of Value, add 1 for true and subtract 1 for false - into a new aggregated table
2. if the result is 1, select the first row for that combination
3. Discard all other rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRAAK3zJxUBRTgkliCJgIEYYk5pciinsVgEceUlMySzPw8pVidaCVDsIyhXklFCYpeM30LfSMDIwMUQUcnZxR+SVFpKtgUIzDXCMMUC31LTFMCCoOwm2IM5hpjMcXQAMOYisgq7KaY4HCLpb6hIYYp6G5JS8wphhhjSpIxgTi8ZAbmYgYv0BTM8EUPXoRjzMF8EwxjQOGCYUyKqxsWx8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Positions" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 6, 24, 45, 61}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Positions", [PromoteAllScalars=true]),
#"Uppercased Text" = Table.TransformColumns(#"Promoted Headers",{{"Value ", Text.Upper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Uppercased Text","true","1",Replacer.ReplaceText,{"IsValueAddition"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","false","-1",Replacer.ReplaceText,{"IsValueAddition"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"IsValueAddition", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"File ", "Value "}, {{"Count", each List.Sum([IsValueAddition]), type nullable number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"File "}),
#"Merged Queries" = Table.NestedJoin(#"Promoted Headers", {"File "}, #"Removed Other Columns", {"File "}, "Removed Other Columns", JoinKind.Inner),
#"Removed Other Columns1" = Table.SelectColumns(#"Merged Queries",{"ID ", "File ", "Date ", "Value ", "IsValueAddition"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1", {"File "})
in
#"Removed Duplicates"
Something like this, just more elegant 🙂
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |