Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SAM190370
Frequent Visitor

Query - Split at capital letter in word

Below you can find an example of a column from which I need to split in two columns.  The split should happen if a Capital letter is surrounded by small letters.  Column 1 and column 2 shows how it should be in the end.

 

 

 

I would like to use a query to do this.

 

Any suggestions?

 

OriginalColumn 1Column 2
Morten Juhl AndersenDitte AndersenMorten Juhl AndersenDitte Andersen
Philip Tu DuongMarie DehnPhilip Tu DuongMarie Dehn
Marlene Dehn  
Line S. Andersen  
Christian Koch  
Daniel Jaro Damm  
Nicklas Jensen  
Simon Dyring Larsen  
Marlene DehnMarie Dehn Marie Dehn
2 ACCEPTED SOLUTIONS
v-huizhn-msft
Employee
Employee

Hi @SAM190370,

After research, I add a "/" before a Capital letter surrounded by small letters using VBA, then split the text using the "/" delimiter in Query Editor. Please follow the steps below.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Insert /  before each capital letter in a cell

 

Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
    Dim I As Integer
    SplitWords = Left(Str, 1)
    For I = 2 To Len(Trim(Str))
        If (Asc(Mid(Str, I, 1)) > 64) And _
           (Asc(Mid(Str, I, 1)) < 91) And _
           (Mid(Str, I - 1, 1) <> " ") Then _
            SplitWords = SplitWords & "/"
        SplitWords = SplitWords & Mid(Str, I, 1)
    Next
End Function


3. Save and close this code, go back to the worksheet, please enter this formula: =splitwords(A2) into a blank cell adjacent your data, see screenshot:

1.PNG

4. Get data from excel, and use the new column to split column. 

2.PNG

5. Creat custom column replace the value where New.2 equals to null. Then remove the New.1, order the columns, finally you get the expected result as the following screenshot.

add custom columnadd custom columnexpected resultexpected result

Here is my Query statement, you can check it in details.

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-huizhn\Desktop\case status.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column2", "New"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "New", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"New.1", "New.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"New.1", type text}, {"New.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [New.2] = null then null else [New.1]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Original", "New.1", "Custom", "New.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"New.1"})
in
    #"Removed Columns"


Best Regards,
Angelia

View solution in original post

Hi Sören,

pls find the file enclosed with the 1st 200 rows of your table. It does what I expected it to do, but maybe my understanding was wrong:

https://1drv.ms/u/s!Av_aAl3fXRbehastWn_7iVvVOWbW8g

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
v-huizhn-msft
Employee
Employee

Hi @SAM190370,

After research, I add a "/" before a Capital letter surrounded by small letters using VBA, then split the text using the "/" delimiter in Query Editor. Please follow the steps below.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Insert /  before each capital letter in a cell

 

Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
    Dim I As Integer
    SplitWords = Left(Str, 1)
    For I = 2 To Len(Trim(Str))
        If (Asc(Mid(Str, I, 1)) > 64) And _
           (Asc(Mid(Str, I, 1)) < 91) And _
           (Mid(Str, I - 1, 1) <> " ") Then _
            SplitWords = SplitWords & "/"
        SplitWords = SplitWords & Mid(Str, I, 1)
    Next
End Function


3. Save and close this code, go back to the worksheet, please enter this formula: =splitwords(A2) into a blank cell adjacent your data, see screenshot:

1.PNG

4. Get data from excel, and use the new column to split column. 

2.PNG

5. Creat custom column replace the value where New.2 equals to null. Then remove the New.1, order the columns, finally you get the expected result as the following screenshot.

add custom columnadd custom columnexpected resultexpected result

Here is my Query statement, you can check it in details.

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-huizhn\Desktop\case status.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column2", "New"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "New", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"New.1", "New.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"New.1", type text}, {"New.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [New.2] = null then null else [New.1]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Original", "New.1", "Custom", "New.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"New.1"})
in
    #"Removed Columns"


Best Regards,
Angelia

Hi @v-huizhn-msft,

 

I am full of admiration:-)  This will do the exact job I am looking for.  

 

Thx.

 

/ Søren (Denmark)

In defense of M, I'd like to post one of the possible approaches here:

 

let

// Function to extract split position
SplitPos = (text) =>
let
    Source = Text.ToList(text),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Number", each Character.ToNumber([Column1])),
    AddIndex = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1),
    #"Added Custom2" = Table.AddColumn(AddIndex, "Sum", each if [Index]>0 then if [Number]<91 and AddIndex[Number]{[Index]+1} >=91 and AddIndex[Number]{[Index]-1} >=91 then "Split" else null else null),
    Split = try Table.SelectRows(#"Added Custom2", each [Sum] = "Split")[Index]{0} otherwise null

in
    Split,

    Source2 = YourTable,
#"Added Custom" = Table.AddColumn(Source2, "SplitPosition", each SplitPos([Original])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column1", each if [SplitPosition]<> null then Text.Range([Original],0,[SplitPosition]) else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column2", each if [SplitPosition]<> null then Text.Range([Original],[SplitPosition], Text.Length([Original])-[SplitPosition]) else null), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"SplitPosition"}) in #"Removed Columns1"

You have to replace "YourTable" in step "Source2" by a reference to your query/table.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF,

 

I agree that this solution will be more suitable.  However, I gave it a try but couldn't make it work.  I have attached an example of the "Original" file.  Maybe it works for you?

 

https://drive.google.com/file/d/0Bw9CCgvO54Y9Q0VYRkc4QVNVRjQ/view?usp=sharing

 

/ Søren

Hi Sören,

pls find the file enclosed with the 1st 200 rows of your table. It does what I expected it to do, but maybe my understanding was wrong:

https://1drv.ms/u/s!Av_aAl3fXRbehastWn_7iVvVOWbW8g

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

HI @ImkeF,

 

You understanding was correct and I get it now. It was my understanding of your solution that whas wrong:-)  I understood source as "file" and not "table/query".................

 

Thx.

 

/ Søren

Greg_Deckler
Super User
Super User

That strikes me as a problem that would require regular expressions. You might be able to use R to get you there.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.