I am trying to extract a 4digit year from an alphanumeric column with several string combinations

[rwm4]

Input column is called "title and data

Fritz Haag 2014 Riesling (Mosel)

G7 2012 The 7th Generation Gran Reserva Estate Bottled Cabernet Sauvignon (Loncomilla Valley)

Le Cadeau 2014 Pinot Noir (Willamette Valley)

let
removeChars = List.Transform({33..47, 58..90, 97..122}, each Character.FromNumber(_)),
Source = Csv.Document(File.Contents("C:\Users\rmuse\Desktop\CS513\Winemag\winemag-data-130k-v2.csv\winemag-data-130k-v2.csv"),[Delimiter=",", Columns=14, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"country", type text}, {"description", type text}, {"designation", type text}, {"points", Int64.Type}, {"price", Int64.Type}, {"province", type text}, {"region_1", type text}, {"region_2", type text}, {"taster_name", type text}, {"taster_twitter_handle", type text}, {"title", type text}, {"variety", type text}, {"winery", type text}}),
addCalcOutput = Table.AddColumn(chgTypes, "calcOutput", each Text.Remove([title], removeChars)),
chgTypes = Table.TransformColumnTypes(Source,{{"title", type text}, {"calcOutput", Int64.Type}}),
splitByDelimiter = Table.ExpandListColumn(Table.TransformColumns(addCalcOutput, {{"calcOutput", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "calcOutput"),
filterLengthFour = Table.SelectRows(splitByDelimiter, each Text.Length([calcOutput]) = 4)
in
filterLengthFour

I tried adapting the above code from a previously solved problem but seem to be getting stuck on the error bellow.

This is the error I keep getting

Please help I have spent a few hours on this problem. Thanks

RWM

[BA_Pete · Accepted solution]

Hi @rwm4 ,

You need to properly align your M code. The first reference in each function should be the name of the previous step in order for PQ to work through the steps sequentially:

Pete

[rwm4]

Hi,

 "title" is ingested as one of the columns in my dataset. Desired output is to generate/ add a new column "calcOutput" which displays the extracted year. I have tried the Column from example and tried manually splitting, both options do not produce the desired extraction.

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"country", type text}, {"description", type text}, {"designation", type text}, {"points", Int64.Type}, {"price", Int64.Type}, {"province", type text}, {"region_1", type text}, {"region_2", type text}, {"taster_name", type text}, {"taster_twitter_handle", type text}, {"title", type text}, {"variety", type text}, {"winery", type text}}),

title	calcOutput
Fritz Haag 2014 Riesling (Mosel)	2014
G7 2012 The 7th Generation Gran Reserva Estate Bottled Cabernet Sauvignon (Loncomilla Valley)	2012
Le Cadeau 2014 Pinot Noir (Willamette Valley)	2014
Domaine Cauhapé 2012 Ballet d'Octobre  (Jurançon)	2012
Nefarious 2010 Rx-3 Red Wine Red (Columbia Valley (WA))	2010
Quinta dos Murças NV 10 Anos Old Tawny  (Port)	null
Winter's Hill 2015 Block 9 114 Pinot Noir (Dundee Hills)	2015
Quinta do Portal NV 10-Years-Old Tawny  (Port)	null
Donna Olimpia 1898 2013 Millepassi  (Bolgheri)	1898 2013
Clos Venturi 2015 1769 Carcajolu (Vin de France)	2015 1769
Fox Run 2011 Riesling 10 Hanging Delta Vineyard Riesling (Finger Lakes)	2011

Thanks

[HotChilli]

It's telling you at the stage :

chgTypes = Table.TransformColumnTypes(Source,{{"title", type text}, {"calcOutput", Int64.Type}}),

There is no column called "title".  That should be fairly straightforward to find out.

-----

Do you want us to debug the code you've posted (will need proper sample data not just the data provided above) OR do you want some help on the problem in the post title (If so, show us what the desired result is)?