cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Create Unique Index Of Two Fields Yields Duplicates

Hi,

I have data from a survey.
The survey has ~350 questions..

There are variable number of responses to choose from based on the question.

One question is which state do you work so there are 52 possible responses (Territories)

I created an index for the questions and a separate index for the responses.

The responses re-seed at 1 for every new question.

I tried to concatenate the question index with the response index so for the state question, this gave me complete Index values

2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8. 2.9 2.10, ...

I then converted the data type to decimal and now I have duplicates at 2.1, 2.2, 2.3, 2.4, and 2.5

How can I create a unique non-repeating index in this case?

Thanks,

-w

1 ACCEPTED SOLUTION
Helper V

Thanks Tom,

I ended up mutiplying by 0.01 for all values less than 10.
I think this pattern will work for all combinations of Questions/Answers, not just this case where there are 52 possible responses.

Thanks,
-w

if
[PK] < 10
then
Number.ToText( [Group] + ( 0.01 * [PK]))
else
Number.ToText([Group]) & "." & Number.ToText([PK])

2 REPLIES 2
Super User

Hey @UncleLewis ,

Maybe you have to check your ToDecimal conversion, as "2.4" and "2.40" will be converted to the same decimal number:2.4. I would suggest that you convert the answers to a fraction, meaning "2.4" will become "2.04" and "2.40" will become 2.4.

Hopefully, this provides you with an ideas of how to tackle your challenge.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper V

Thanks Tom,

I ended up mutiplying by 0.01 for all values less than 10.
I think this pattern will work for all combinations of Questions/Answers, not just this case where there are 52 possible responses.

Thanks,
-w

if
[PK] < 10
then
Number.ToText( [Group] + ( 0.01 * [PK]))
else
Number.ToText([Group]) & "." & Number.ToText([PK])

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.