Format phone numbers and detect duplicates using Dataflow

[Synth]

Hello everyone,

I have data in excel around 8K records that contains contact information and most importantly the MobileNumber column.

The MobileNumber data is formatted in different ways like below and it's not clean:

+971 05 123 123 123
+97105123123123
97105123123123
9.7105123123123
05123123123

Based on the above example I provided, there are duplicates in the MobileNumber column for the same contact.

What I want to achieve is the below:

1. I want to convert the phone number column and apply one consistent formatting for all the data as below:
   (+###) (###)-(####)
2. After the MobileNumber data is formatted based on Step1 the Dataflow should check if there are duplicates or matching MobileNumber those records should be ignored and the only record that should exist is the record that has the right format (+###) (###)-(####).

The purpose of the above is when the data is cleaned I want to import it to Dataverse as a new table and the primary column will be the phone number.

Can please someone advise what is the best practice to achieve the above scenario? I would highly appreciate it if someone can provide an example of how to implement it.

I hope that I did not miss any criteria that should be added to the above.

Thank you!

[Synth]

Hello @v-henryk-mstf ,

Any updates on the above?
I look forward to hearing back from you.

Thakn you!

[v-henryk-mstf]

Hi @Synth ,

Based on your description there seems to be some confusion as to whether your purpose is to develop a standard formatted phone number column in powerbi that can be used as a basis for testing imported data. Then tally the correctly formatted data and form a new table? And finally import into Dataverse?

If i have misunderstood you, would you be able to further describe your needs and provide relevant desired outcomes, etc. To facilitate my further testing. Looking forward to your reply.

Best Regards,
Henry

[Synth]

Hello @v-henryk-mstf ,

Thank you for your reply.

Apologies for have not been clear. Please find below the details:

1. For example:
 The below data table has information about some users with different mobile number formats:

2. The purpose is to apply and clean the data as below:

2.1- Apply this mobile number format (+###) (##)-(###)-(####) for each row that exist in the above table.

For example:
In the last row of the above table, the number starts with 5123... so once we apply the above format the final result will be:
(+971) (51)-(123)-(1231)

Another example:
Before the last row we have 9.7105... once the mobile number format is applied to that column:
First, the (.) should be removed from the number and we should apply the same format as above (+###) (##)-(###)-(####)

Hence, for the other records if there are spaces between numbers those should be removed, and if there is/are anything missing from the data like (+) or the parenthesis those should be added and formatted as above.

As mentioned above, multiple conditions should be applied to that column to format all the data.

Once the data is cleaned I want to import it to a Dataverse Table through dataflow.

Is the above feasible to be done through dataflow (Power Query)?
I hope that I have clarified the purpose of the above scenario.

Please let me know if you need any additional information.

Best regards,