brawnystaff
Board Regular
- Joined
- Aug 9, 2012
- Messages
- 109
- Office Version
- 365
I am using the Custom Function below to look for duplicate data in a table by two columns. When invoked, it gives me a drop down to search for Table, but I have to manually input the column names. Any ideas as to modify code to have the column names be drop-down list as well? Tried various declarations with no luck. Thanks.
Power Query:
let Dup = (DataTable as table, CCN as text, ClaimLine as text) =>
let
Source = DataTable,
#"Changed Type" = Table.TransformColumnTypes(Source,{{CCN, Int64.Type}, {ClaimLine, type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {CCN}, {{"Data", each _, type table [CCN=nullable number, ClaimLine=nullable text, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Instance",1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {CCN, ClaimLine, "Index", "Instance"}, {CCN, ClaimLine, "Index", "Instance"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Occurrence", each if [Instance] = 1 then "Original" else "Duplicate"),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Instance"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{CCN, Int64.Type}, {ClaimLine, type text}, {"Occurrence", type text}})
in
#"Changed Type1"
in
Dup