Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SVEBE
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? 

 

Thx in advance! 

11 REPLIES 11
rsbin
Super User
Super User

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

 

 

 

rsbin_0-1685991368772.png

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

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

SVEBE_0-1686923979824.png

 

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?

@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
Regular Visitor

I get this as a reply when trying to load: 

SVEBE_0-1686037258389.png

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)

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

SVEBE_1-1686055938945.png

 

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

 

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.