CODEWITHSUNDEEP
excelpowerbipowerquery
Nahuatl
Nahuatl

Reputation: 132

Extract Month from String Power Query

I've got the following strings.

String1: MAY 25 TVM HKNYLON3NG

String2: 17 JAN 21 YWE TT GWS DLL

String3: HDX_XX_ZZZ APR 21

So this is simple with a function such as Left(string, 3) or mid(string, 4, 3). However, the months will change depending on how people enter the info. So daily I check for errors and update the functions. Now that I'm refactoring the spreadsheet using power query, I wanted to see if there was a way of creating a function which matches a list of months to the string.

So code will check if JAN, FEB, MAR... is present and return that month.

String1 output: MAY

String2 output: JAN

String3 output: APR

Because the text string isn't consistent, I can't implement something like this I wrote a few years back.

Upvotes: 0

Views: 809

Answers (1)

horseyride
horseyride

Reputation: 21318

see https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

Load an array of the desired month names to find.

Add a new column using the algo below, to locate instances of any items in that array

//see https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842
Table2 = Table.Buffer(#table({"month"},{{"Jan"},{"Feb"},{"Mar"},{"Apr"},{"May"},{"Jun"},{"Jul"},{"Aug"},{"Sep"},{"Oct"},{"Nov"},{"Dec"}})),
RelativeMerge = Table.AddColumn(#"priorstepname", "RelativeJoin", (Earlier) => Table.SelectRows(Table2, each Text.Contains(Earlier[yourtextcolumnname],[month], Comparer.OrdinalIgnoreCase))),
#"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"month"}, {"month"})

Upvotes: 1

Related Questions