cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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. 

 

 

10-12-2016 1-38-12 PM.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

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

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.

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
Highlighted
Super User IX
Super User IX

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

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.

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Solution Sage
Solution Sage

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

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

Highlighted
Helper III
Helper III

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


@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?

Highlighted
Super User IX
Super User IX

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

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper III
Helper III

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


@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.

Highlighted
Helper III
Helper III

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

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. 

 

10-13-2016 10-33-44 AM.png

 

Highlighted
Super User IX
Super User IX

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

@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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper III
Helper III

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

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).

 

Letter.pngRelationship.png

 

What should my relationship be?

 

 

 

 

 

 

Highlighted
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors