Forum Discussion

FalconOz's avatar
FalconOz
New Member
4 years ago

Best practices for Creating unique customer id?

I get a flatfile from my online store with hundreds of orders. Some customers - the repeating ones - create a profile and purchase always from the same username. Others buy sporadically and don't bother to create a profile, instead purchasing as a guest.

I've got a lot of their data, such as addresses and phone numbers from the shipping details, but addresses are sometimes also not the same, eg. Unit 1 at 22 apple st, vs U 1 at 22 Apple street.

My data entry team is happy to do some manual data cleaning for a few records, but looking backwards we have a lot of records to clean and ideally we should try to do fix it with some Power Query M code.

My question is: as we identify different clients, particularly with different names, should I keep this on a separate table, outside Power query.

Anyone can share how they handle similar situations?

Need Help

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi FalconOz 

     

    It is recommend to have a separate customer table as a dimension table. There should be a unique ID column in the customer table to distinguish every customer. The customers who don't bother to create a profile should also have unique IDs. 

     

    When cleaning data, you may try the Fuzzy merge feature. It is part of Merge Queries feature. You need a standard table in advance, then merge columns like address to it with fuzzy merge.

     

    Best Regards,
    Community Support Team _ Jing