M-Code - extracting words from the description

Power Query
1

Dear DNA Team,

I will appreciate your guidance or help as I am stuck and I am not 100% sure how can I tackle the topic.

Please find the attached file:

TextSplit.xlsx (17.4 KB)

From the table below:

image
image920×178 12.8 KB

The end result that I would like to get is:

image
image1411×341 9.71 KB

I am only able to create the end result only for the rows 3-5 but not 1 till 5 using the example data table.

First I have added index o keep track of lines and I use Text.Split by seperator “-”.

Unfortunately, the row 1-2 don’t have any separator and the only way I thought to separate the wors is by using: Split Columns by Positions 0,57,77.

Do you have any idea how could I get the end result?

Thank you ,

Matty

2

Hi @Matty,

Give this a go, just paste the script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDdasQgEIVfZfBak9HRGC+XxBah2bQ6gYWQ93+Nul1S+rPJ3WHgfN/RdRWklVYGjRZStDlOqV0Kj+3wNvOSI1wWIA2owWhIV445coEhxzFxXHIBZyB08JIvqcAYb1wDQweI3zd4jYXTfIWKwbZaXJChE5tchemVOXebHtAcuckD2b9uDe7ETUH29st98O5Hk3OaftZrHObpnSN80B1jcGf4nfHrwxr0zcFojZKC2hfCPpskonqy+36udG1r6+FEFZ45scFw5CQnnf/vtNKdOW0NXmzbJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Description = _t, Amount = _t]),
    AddCustom = Table.AddColumn( Source, "Text", each 
        if Text.EndsWith([Description], "-") then List.Transform( List.RemoveMatchingItems( Text.Split( [Description], "-"), {"", null} ), Text.Trim ) else 
        let  
            myDescr = [Description], 
            mySplit = Splitter.SplitTextByAnyDelimiter( {"0".."9"} ), 
            myList = List.Select( mySplit(Text.RemoveRange([Description], Text.Length([Description])-1)), each Value.FromText(_) is text ), 
            SkipAfter = List.Select( myList, each Text.EndsWith( Text.Trim(_), " AU" )), 
            lPos = List.PositionOf( myList, SkipAfter{0}? ), 
            FinalList = if lPos = -1 then myList else List.RemoveItems( myList, {myList{lPos+1}} ), 
            Result = Splitter.SplitTextByPositions( List.Transform( FinalList, each Text.PositionOf( myDescr, _))) 
        in 
            List.Transform( Result( myDescr ), Text.Trim )
    ),
    ExpandText = Table.ExpandListColumn(AddCustom, "Text")
in
    ExpandText

.
It’s not pretty but does seem to do the trick.
Here’s the result.

image
image1329×306 34.4 KB

I hope this is helpful

2 Likes
3

Thank you @Melissa it worked on smaller scale of the data, but on larger scale I get duplicates :frowning: I think the issue is when I have blank instead of “-” as an separator.

I have an question , would be interested in giving private lesson regarding m-code? Obviously not for free :slight_smile: ?
Thank you,

Matty

4
5

Thanks @DavieJoe for supplying a link to the PQ M Course. :+1:

.
@Matty, I hope you’ll enjoy that.
Q: Can you provide a larger sample that generates this unwanted duplication so I can look into that?

All the best.

1 Like
6

Hello @Matty, a gentle follow up on Melissa’s request. :slight_smile:

We noticed that no response from you was seen since October 23rd.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @Matty, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.

1 Like
8

@Melissa Apologise for coming back , I just read this now.

To be honest, I was not logging for some time, I was studying towards my ACCA exams.
I could provide it, but around in January :frowning: . Currently I am just too busy with work and exams :frowning: .

Thank you,
Matty

9

No worries @Matty,
Just create a new, follow up topic when your ready.

Best of luck with your exams!

1 Like