cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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?

11 REPLIES 11
Super User

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,

Regular Visitor

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

Super User

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,

Regular Visitor

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?

Super User

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?

Regular Visitor

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)
Super User

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,

Regular Visitor

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"

Super User

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?

Regular Visitor

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

Regular Visitor

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.