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 need to write a power query for a text filed if the same data is repeated in another cell in same column needs to print as duplicate in new column else print unique.
Ex. if phone number is repeated again in same column, in new column it needs to print as duplicate else unique
Solved! Go to Solution.
Look here for steps which you can incorporate
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxDcAwCAR3oXbDm1hunYyB2H+NPDYuXrqTONxFVZosjhDNBZ38coT0x8gfR0hX3HtsH6OckN5LT207T7fqgfv/9HOWEyJ+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Term_Guid = _t, Term_Name = _t, Term_XYZ = _t]),
#"Grouped Rows" = Table.Group(Source, {"Term_Name"}, {{"Term_Name_Count", each Table.RowCount(_), Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Term_Name"}, #"Grouped Rows", {"Term_Name"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Term_Name_Count"}, {"Term_Name_Count"}),
#"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [Term_Name_Count]>1 then "Duplicate" else "Unique"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Term_Name_Count"})
in
#"Removed Columns"
Look here for steps which you can incorporate
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxDcAwCAR3oXbDm1hunYyB2H+NPDYuXrqTONxFVZosjhDNBZ38coT0x8gfR0hX3HtsH6OckN5LT207T7fqgfv/9HOWEyJ+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Term_Guid = _t, Term_Name = _t, Term_XYZ = _t]),
#"Grouped Rows" = Table.Group(Source, {"Term_Name"}, {{"Term_Name_Count", each Table.RowCount(_), Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Term_Name"}, #"Grouped Rows", {"Term_Name"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Term_Name_Count"}, {"Term_Name_Count"}),
#"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [Term_Name_Count]>1 then "Duplicate" else "Unique"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Term_Name_Count"})
in
#"Removed Columns"
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |