Additional column that has values based on the first letter of another column

[JCBI1023]

Hello,

I have 3 collectors: collector 1, collector 2, and collector 3. Each collector is assigned a customer based on the first letter of that customer. 

If the cust. name starts with #,N,P,Q,S,T,U,V,W,Y,Z = Collector 1

If A,B,E,I,K,L,M = Collector 2

If C,D,F,G,H,J,O,R = Collector 3

I have a cust. name column. I'd assume that I would need to create a new column titled "Collector" and apply a formular to it that satisfies what I detail above.

Thank you for any information. 

[Sunnie]

I have a similar need.  I have a column that has all of my applications.  This column is very long, and causes my table visuals to be too long to copy and paste in an email body message, which is what my organization wants to see, not in an Online Power Bi report, but in the email.  Well, to capture that list of applications I have to cut and paste it in the email.

How can I have Power BI split the table into two columns, such as 1st column is (first letter of application name is A , B, C,,, all the way to M), and 2nd column is (first letter of application name is N, O, P,,, all the way to Z).

I made two tables : Title by A - M, and Title by N - Z.  I know I need to have a relationship, so how do I copy the list of Applications from the my main table (titled Applications), over to each of those two tables, so I can create the relationship.

thanks,

[Sunnie]

Hi, this is solved.  Using Column by Example, data in new column is by letters' A-Z corresponding to the app name in the original column, then with the new column created two groups , of 1) A-M, 2) N-Z. and now can use this new grouping on my viz filter , this shortens the table rows.

[blopez11]

One way you could achieve would be to create a formula, i.e. f_CustToRepMapper, then create a new column and call as:

f_CustToRepMapper ( Text.Starts ( [Cust. Name], 1 ) )

formula may look like this:

let
f_CustToRepMapper= (input) => let
values = {
{"N","Collector 1"},
{"P","Collector 1"},

..
{"A","Collector 2"},
{"B","Collector 2"},

..
{"C","Collector 3"},
{"D","Collector 4"},
...
{input, "<UNK>"}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result
in f_CustToRepMapper

[Greg_Deckler · Accepted solution]

I believe that what you would want would be to create a mapping table like:

Collector,Letter

Collector1,#

Collector1,N

Collector1,P

Collector1,Q

...

Collector2,A

Collector2,B

Collector2,C

...

etc. Then you could create a calculated column in your Customer table, "First Letter = LEFT([Cust. Name],1)". Then you could relate the First Letter and Letter fields and you have your mapping of customers to collectors.

[JCBI1023]

---

@smoupre wrote:

I believe that what you would want would be to create a mapping table like:

Collector,Letter

Collector1,#

Collector1,N

Collector1,P

Collector1,Q

...

Collector2,A

Collector2,B

Collector2,C

...

etc. Then you could create a calculated column in your Customer table, "First Letter = LEFT([Cust. Name],1)". Then you could relate the First Letter and Letter fields and you have your mapping of customers to collectors.

---

Thank you for your reply. I apologize, I am a beginner when it comes to Power BI. How do I create a mapping table? Is this just another sheet within my Excel document?

[Greg_Deckler]

You could do it that way and just import it into Desktop. You could also just use an "Enter Data" query and create it that way. Or create a CSV file and import it. Lots of different ways to do it but in the end, you want a table in your data model that has those two columns and every combination of Collector and first Letter.

[JCBI1023]

---

@smoupre wrote:

You could do it that way and just import it into Desktop. You could also just use an "Enter Data" query and create it that way. Or create a CSV file and import it. Lots of different ways to do it but in the end, you want a table in your data model that has those two columns and every combination of Collector and first Letter.

---

Excellent. I will try this now.

[JCBI1023]

OK.  I have created an addtiional table using the 'Enter Data' feature. I now have a 'Collector' table and a 'Customer' sheet.

Now I need to create a calculated column within my 'Sheet1'. Can you further assist with that formula? Thank you. 

[Greg_Deckler]

@JCBI1023 - Sure, what are you trying to calculate? Are you referring to your column to create a relationship on? That would be:

Letter = LEFT([Cust. Name],1)

Then you can relate the two tables.

If that is not what you are trying to calculate, let me know.

[JCBI1023]

Thanks so much. I just want to archieve this in a seperate column, so my values show who the collector is.

If the cust. name starts with #,N,P,Q,S,T,U,V,W,Y,Z = Alice

If A,B,E,I,K,L,M = Laura

If C,D,F,G,H,J,O,R = Diana

As you suggested, I created another column titled 'Letter' with the value Letter = LEFT([Cust. Name],1).

What should my relationship be?

[blopez11]

You don't need an extra column now that you have these two tables related

Not sure what story you want to tell with your data

But for example, you can create a pie chart, and have Collector from your Collector table in the Legend and Cust. Name from your Sheet 1 table to maybe see count of customer touches by collector

Without seeing your data or know what you want to project, hard to tell what visualization you should use

[JCBI1023]

Thanks for your reply.

It's a simple AR Aging Report. My spreadsheet has customer information but it does not show how the collector of the customer is.

Sans going through each line of data and typing in who the collector is, I want it to populate automatically.

[JCBI1023]

Got it! Thanks everyone.