Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I have a table with the following columns:
ID, Email, Phone 1 and Phone 2.
All the IDs are unique. I need to assign a unique index to each record. However, the records that either have the same Email or the same Phone will get the same index. Records with the same Phone 1 in the Phone 2 column, or the same Phone 2 in the Phone 1 column will also get the same index. I need a way to achieve this in Power Query. I tried different codes in M language but it didn't work out.
The error was fixed, but the output of the code above is a column that assigns a unique index to each row whether it has similarities with other columns or not.
= let fx=(rcd)=>let a=Record.ToList(rcd) in Text.Combine(List.Transform(List.FirstN(a,2)&List.Sort(List.Skip(a,2)),Text.From),"|"),
grp=Table.Group(YourTableName,Table.ColumnNames(YourTableName),{"n",each _},1,(x,y)=>Value.Compare(fx(x),fx(y)))
in Table.Combine(Table.TransformRows(Table.AddIndexColumn(grp,"idx"),each Table.AddColumn([n],"idx",(x)=>[idx])))
I changed the table name with mine and I get the error below :
Expression. Error: A cyclic reference was encountered during evaluation.