PowerQuery Function

Power Query
1

Hi,

I have been watching Melissa’s “Creating a custom text cleaning function in PQ” and I have managed to create an easy function. I have a lot of columns to clean up and am hoping to create another couple of functions but need some help please.

This is an example of my data:

image

I want to transform that one column into two columns:
image
The first extracts the text prior to the first bracket or if no bracket, just uses the text.
The second combines “SR” with the text between the first set of brackets. If there are no brackets, it returns blank.

I have managed to do this in pq by creating two new columns from examples but I would prefer it as a function as i have to use it for many columns.

My mcode is:
let
Source = Excel.CurrentWorkbook(){[Name=“Table5”]}[Content],
CustColumnName = Table.AddColumn(Source, “Name”, each let splitMgt = Splitter.SplitTextByDelimiter(" (", QuoteStyle.None)([Mgt]), splitsplitMgt0 = Splitter.SplitTextByDelimiter("(", QuoteStyle.None)(splitMgt{0}?) in splitsplitMgt0{0}?, type text),

CustColumnSRNumber = Table.AddColumn(CustColumnName, "SRNumber", each let splitMgt = Splitter.SplitTextByDelimiter("(", QuoteStyle.None)([Mgt]), splitsplitMgt1 = Splitter.SplitTextByDelimiter(")", QuoteStyle.None)(splitMgt{1}?) in Text.Combine({"SR", splitsplitMgt1{0}?}), type text)

in
CustColumnSRNumber

File is attached. Thanks for looking

Test function.xlsx (43.8 KB)

2

Hi @KimC,

Great to see you’re having fun with PQ :smiley:
Give this function a go, it generates a Record with the new fields, you can expand.

( t as text ) as record =>
let
    SplitL = Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false),
    SplitR = Splitter.SplitTextByEachDelimiter({")"}, QuoteStyle.Csv, false),
    GetName = SplitL( t ){0}?,
    GetNumb = Text.Combine( {"SR"} & { SplitR( SplitL( t ){1}?){0}? } )
in 
    [Name = GetName, SRNumber = GetNumb]

I hope this is helpful

3

I am enjoying creating my own functions :sunglasses: thanks to your great video and thanks for the above code. I will give it a go.

4

Hi @KimC, did the response provided above help you solve your query?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!