Finding and deleting Types of business entity from organization names

[Anonymous]

Hi there,

I have a list of organizations around the  globe.  Some of them are ended with types of their business entity, e.g. Star LLC, Moon LTD.

Is there a smart way to delete these business entities endings?

I used the conditional formatting columns, find & replace, the Substitute function. But there should be more sophisticated way to solve it.

My challenges are:

• There are more than 30 types of business entities, including variations with commas, dots, spaces. 
• Most probably, I don't know about all possible options.
• Sometimes an organization name contains INC e.g., but this is not the business entity, just a part of an organization. I don't  want to replace anything in this case.
• I want to delete commas, but some organizations contain them as a part of their name.

[BhaveshPatel]

Hi There,

You can use powerquery to delete this. If possible, Post the sample file and we can provide you the exact solution.

Thanks & Regards,

Bhavesh

[Anonymous]

Bhavesh,

Thank you so much!

It would be great.

I didn't find the way to attach the file but here is the list of endings from my file:

, inc.	Company , inc.
, inc	Company , inc
inc.	Company inc.
inc	Company inc
, llc.	Company , llc.
, llc	Company , llc
llc.	Company llc.
llc	Company llc
, ltd.	Company , ltd.
, ltd	Company , ltd
ltd.	Company ltd.
ltd	Company ltd
, BV	Company , BV
BV.	Company BV.
BV	Company BV
AB	Company AB
AS	Company AS
B.V.	Company B.V.
Gmbh	Company Gmbh
ApS	Company ApS
A/S	Company A/S
K/S	Company K/S
P/S	Company P/S
Ay	Company Ay
Ky	Company Ky
Oy	Company Oy
Oyj	Company Oyj
osk	Company osk
AG	Company AG
S.A.R.L.	Company S.A.R.L.
nv	Company nv
Sàrl	Company Sàrl
PLLC	Company PLLC
corporation	Company corporation
corp	Company corp
corp.	Company corp.
incorporated	Company incorporated
group	Company group
technologies	Company technologies
company	Company company
solutions	Company solutions
software	Company software
Entertainment	Company Entertainment
Services	Company Services
Systems	Company Systems
Agency	Company Agency
* * *	Company * * *
|	Company |
\	Company \
/	Company /
,	Company ,
-	Company -
	Company | Some text we need to delete
	Company / fdfw
	TTP  | MACH | The Turning Point
	Test® \ company

There are also organizations' names without these endings. They should stay the same.

Thank you!

[Anonymous]

@v-haibl-msft

Thank you so much for that detailed plan!

I tried to replicate all the steps. But I failed to create the crossjoin table 😞

I tried to add the formula as a blank query, It didn't work. Where in Power BI editor should I add this? 

I found this article, https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/

Here is the screenshot: http://i.imgur.com/SaaT5NR.png

[v-haibl-msft]

@Anonymous

Please first close and apply Query Editor, I'm not using Power Query here. You can use DAX formula like below.

Best Regards,

Herbert

[Anonymous]

@v-haibl-msft

Thank you! I'll try asap and write  how did go.  I am sorry for my slow replies. It is very hectic for me now. I do appreciate your help! Thank you so much 🙂

[v-haibl-msft · Accepted solution]

@Anonymous

I tried to do it with DAX formulas. It is actually depending on your true organization names. I created two tables with following formulas. Please try it in your data. Maybe we cannot get the expected result for a few percent data because of data variations.

I create two tables for my original data.

Then I create a new crossjoin table and a new column in it with following formulas.

FullTable =
CROSSJOIN ( Table1, Table2 )

Organizations = 
VAR TempName =
    IF (
        SEARCH ( FullTable[business entity], FullTable[FullCompany],, 0 ) > 0
            && SEARCH ( FullTable[business entity], FullTable[FullCompany],, 0 )
                + LEN ( FullTable[business entity] )
                - 1
                = LEN ( FullTable[FullCompany] ),
        REPLACE (
            FullTable[FullCompany],
            SEARCH ( FullTable[business entity], FullTable[FullCompany],, 0 ),
            LEN ( FullTable[business entity] ),
            ""
        ),
        ""
    )
RETURN
    (
        IF (
            SEARCH ( ",", TempName,, 0 ) = 0,
            TempName,
            IF ( SEARCH ( ",", TempName,, 0 ) = LEN ( TempName ) - 1, "" )
        )
)

At last, create another table with following formula.

FinalTable = 
CALCULATETABLE ( FullTable, FullTable[Organizations] <> BLANK () )

Best Regards,

Herbert