Need to join 2 columns together (column 1 = number, column 2 is initials)

[SVEBE]

Hi,

I need to be able to join 2 columns.  

Column 1 = invoice number and column 2 = initials of the responsible person.

Extra remark: I DO have in my database: 

- responsible person

- invoice number.

What I do not have = the initials of said responsible person... I'm completely new to this... How do I proceed? 

Thx in advance! 

[rsbin]

@SVEBE ,

Best to proceed in a step wise fashion.  First create a new Calculated Column to determine the Initials.

You can use a number of different String functions to do this.  These are the same as in Excel if you are familiar with those.

Initials = LEFT( [Name], 1 ) & MID( [Name], ( FIND( " ", [Name],1 ) + 1 ), 1 )

The above formula assumes your Name column only has first and last names.  If you have middle names or initials, you will have to modify the String functions accordingly.

Then create second calculated column to concatenate your Invoice Number to this new Initials column

New Column = [Invoice Number] & "-" & [Initials]

Hope this gets you started in the right direction.

 Regards,

[SVEBE]

Hi,

is it possible i get the error, because there is one name in there that has a middle name? (my name....), however: we only need to have 2 digit initials... so for instance "Eddy De Vinck" would be EV... . 

[rsbin]

@SVEBE ,

If you read my original reply, I indicated that my quick formula would need to be modified if middle names or initials existed.  To help understand better,

create another calculated column to determine number of spaces:

Spaces = LEN( [Name] )-LEN(SUBSTITUTE( [Name], " ", "" ))

Then create a SWITCH statement ( similar to an IF):

Initials = SWITCH(
           TRUE(),
     [Spaces] = 1, LEFT( [Name], 1 ) & MID( [Name], ( FIND( " ", [Name],1 ) + 1 ), 1 ),
      LEFT( [Name], 1) & MID( [Name], ( FIND( " ", SUBSTITUTE( [Name]," ", "-", 1 ), 1 ) + 1 ), 1 ))

 If you are the only one with a middle name, you can simply use Power Query to edit the middle portion out. But the above takes account of those names with two spaces.

Feel free to study DAX TEXT functions to help you understand what the above formulas are doing.

Hope this helps.

Regards,

[SVEBE]

Hi,   

Thx again (for your time ànd patience!!!) 

I tried that & unfortunately still get an error when loading it in my table.  

Now; "name" is called "Employee Responsible", but I did always update.  When I try the first part of your above suggestion: the Spaces calculation indeed works.  So for myself & other people with 3 names, it shows "2" as a result.  

But then when i try the full "Initials" ... It accepts the formula, but then when i try to load it in PowerBI, I get this: 

Terribly sorry for this ... Is there a way I can share my screen with you maybe?  Without having to post company data/info on here?

[rsbin]

@SVEBE ,

Unfortunately, I am not in a position to do a screenshare with you.

Is there anyway you can post fictitious data?   Just use as many examples as feasible and replace actual names with fictitious characters.

If I try to interpret the error message, I believe it is saying that it cannot find a "space" character

(" ") in one of your [Employee Responsible] values.  Are there any instances of this?

[SVEBE]

I get this as a reply when trying to load: 

the text i entered 

Initials = LEFT('Sales Related Documents'[Employee Responsible],1)&MID('Sales Related Documents'[Employee Responsible],FIND(" ",'Sales Related Documents'[Employee Responsible],1)+1,1)

[rsbin]

@SVEBE ,

I think you are missing a closing parentheses after the "+1".

Mine: 

( FIND( " ", [Name],1 ) + 1 ), 1 )

  FIND(" ",'Sales Related Documents'[Employee Responsible],1)+1,1)

Regards,

[SVEBE]

I am so sorry (really feel like an idiot now) but: 

it now doesn't give me an error in the formula, but i do get this when i try to drag the column into my view (to see if it works): 

Translation: "can't load the data for this visual element" 

[rsbin]

@SVEBE ,

Can you post a small sample of your data table with your new calculated column included?  Please post as Text so it can be easily copied.

In which Visual are you trying to use this new [Initials] column?

[SVEBE]

Is this what you mean?  First column is just a company code we use.  Second column = my loading reference + initials of the employee responsible, followed by product name (in example pencils & crayons), quantity in MT, loading date, mode of transport, blending instructions, employee responsible (of whom the initials need to be created)

Company Code,Delivery Number CPC,Product Name,Quantity in MT,Estimated Loading Date,Mode of Transport,Blending Instructions (% of material per grade),Employee Responsible

MA,80908390 SF,PENCILS,22.96,2023-06-01 00:00:00,Truck,,Solas Francos

MA,80908391 SF,PENCILS,22.96,2023-06-07 00:00:00,Truck,,Solas Francos

CA,80929851 DA,CRAYONS,20,2023-06-30 00:00:00,Truck,,De Peeters Alain

[SVEBE]

Hi,

unfortunately, i'm not allowed to copy/paste company info on a forum :-(... . 

I'll try you rabove suggestions again later this week - very busy at the moment - & let you know.

The visual is a tabel.