How do I combine items with different names?

[CraigBlackman]

I have a report that shows the sales of customers. Over time the name of the customer has changed however our database retains the name at the time of the order being placed for histroical purposes. 

So I have 3 different company names over the last 5 years, however they are all one and the same customer, just different headers.

I have tried simply replacing the values for the different names to the same, however I then get a lot of blank records.

Any other suggestions?

Thanks

Craig

[greggyb · Accepted solution]

If you don't care about the name at time-of-sale, but only want to plot current name, then there's no need to deal with a type 2 SCD. Type 1 would be sufficient (how you track in the source is irrelevant to how you handle in the analysis and presentation layers).

If you can utilize [CustomerID] for all of the relationships in your model that have to do with customers, you can just store a single value for [CustomerName] in your customer dimension.

You can utilize SQL or Power Query to find the most recent name for a given [CustomerID]. If it's not important to preserve a specific, most recent name, you could even take a shortcut by using the 'Remove Duplicates' transformation in Power Query - this would non-deterministically remove all but one [CustomerName] associated with each [CustomerID]. It may even be deterministic with an appropriate sort order (unsure how the transformation is implemented in PQ).

[GTR]

I would also suggest looking into type 2 slowly changing dimensions in your data model. Hopefully this leads you in the right direction as an alternative if the other ideas do not work out. 

[kcantor]

Use the Querry Editor to replace the name in the data after it is uploaded.  Use the customer account number to pull the numbers. Create a calculated column building on an if statement so that all other customer names are pulled in without change. That customer's name is changed. Then, use that column for building reports displaying name.

[Seth_C_Bauer]

@CraigBlackman You could create a seperate Dimension table that rolls the names up to the parent company, in that respect it might be easier to determine missing companies that may be causing your blanks as well. So every company would be in your dimension table, and some would roll up to a parent and others would roll up to themselves...

Something like:

company Parent

A                    1

B                    1

C                    2

D                    3

[CraigBlackman]

And how would I do that exactly?

Thanks for you help.

[Seth_C_Bauer]

@CraigBlackman

What is your backend, SQL?

Can you show a sample of what your data looks like now?

Ideally, I would try to do this on the database side, is that possible in this case?

[CraigBlackman]

@Seth_C_Bauer,

It is indeed a SQL backend

What would you like to know in terms of how it looks?

[Seth_C_Bauer]

@CraigBlackman I would recommend doing one of two things.

1) Depending on how your data is set up, and if you need to see the different company names over time - then you would want to look at slowly changing dimensions as @GTR suggests. That assumes alot about your backend though.

2) The reason I asked for a sample, was I don't know how the data is set up in the db. Do you have a relationship between the Company Name you want to use and the other company names? ie. Can you roll up the sales orders to the overall company? Or is this a case where the Company Name is just "known" on the business side?

Depending on the answer, there are a number of ways to accomplish this in a more re-usable way then doing it on the front end in Power BI.

Create a mapping table that updates via a process and use that as a dimension (AccountiD, CompanyName | DiffCompName)

Create a view that rolls up sales to the Company Name (if there is a relationship)

I would aim for a solution that allows you to do quick adds, rather than re-do the entire process and have to manually reload data.

[CraigBlackman]

@Seth_C_Bauer

All customer have a unique id, so albeit the actual name has changed, this is always retained. So there are a number of relationships we can employee.

[greggyb · Accepted solution]

If you don't care about the name at time-of-sale, but only want to plot current name, then there's no need to deal with a type 2 SCD. Type 1 would be sufficient (how you track in the source is irrelevant to how you handle in the analysis and presentation layers).

If you can utilize [CustomerID] for all of the relationships in your model that have to do with customers, you can just store a single value for [CustomerName] in your customer dimension.

You can utilize SQL or Power Query to find the most recent name for a given [CustomerID]. If it's not important to preserve a specific, most recent name, you could even take a shortcut by using the 'Remove Duplicates' transformation in Power Query - this would non-deterministically remove all but one [CustomerName] associated with each [CustomerID]. It may even be deterministic with an appropriate sort order (unsure how the transformation is implemented in PQ).

[CraigBlackman]

@greggyb

Simply I just want to roll up and use only 1 customer name per customer_id. A simple find and replace against the customer name in each order header is not sufficient as it leaves blanks.

[Seth_C_Bauer · Accepted solution]

@CraigBlackman Since you have the account_id, I'd just create a mapping table which will be your dimension. Each account_id and the company name like @greggyb suggests. 

You could easily create a process after the initial table creation to check / add new companies going forward. (based on the assumption that the initial company name would stay going forward.)

[CraigBlackman]

@Seth_C_Bauer, @greggyb

I have over thought this to an extent as there is already a customer dimension which I have now added a relationship too and that has sorted out my problem.

Thanks you all for your input, really really appreciated.