This is my sample column Table. As you can see correction notes start from a date. I want to extract that date from each row. It may be in MM/DD/YYYY or MM/DD/YY or DDMMMYYYY format. How is it achievable?Project NameCorrection NotesLTR1/13/25 - parts added/reserved to SO 134931. JWG 11/6/24: controlnet connectors are called out in the interconnect box. i think the RJ45 connectors should follow suit to keep consistency. Need to remove item 55 from BOM and add RJ45 connector to 211177. -GJHOLMESPGR1/9/2025: Redline Released -GJHOLMES 11/6/24: Items 51-56 see to be added under REV D as a designated conduit run for the vision system. This system was originally COAX but has now changed to EthernetGPR01jan2025 This system was originally COAX but has now changed to EthernetThank You,Ajit

Hi ajitsahoo8338 Try this in custom column: Simple version (Just Extract The First Date Before Delimeter space, colon etc and then fix the date type): List.First( List.Transform( Text.SplitAny([Correction Notes], ": "), each Text.Trim(_) ) ) Output: Complete version : let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Correction Notes", type text}}), // Extract the first date segment before any delimiter (-, :, space) #"Added ExtractedDateText" = Table.AddColumn(#"Changed Type", "ExtractedDateText", each Text.Trim( Text.Start( [Correction Notes], let text = [Correction Notes], delimiters = {"-", ":", " "}, positions = List.Transform(delimiters, each Text.PositionOf(text, _, Occurrence.First)), validPositions = List.Select(positions, each _ >= 0), minPosition = if List.IsEmpty(validPositions) then Text.Length(text) else List.Min(validPositions) in minPosition ) ) ), // Parse the extracted date text using multiple formats #"Added ParsedDate" = Table.AddColumn(#"Added ExtractedDateText", "ParsedDate", each let dateText = [ExtractedDateText], tryFormat1 = try DateTime.FromText(dateText, [Format="M/d/yyyy", Culture="en-US"]), tryFormat2 = try DateTime.FromText(dateText, [Format="M/d/yy", Culture="en-US"]), tryFormat3 = try DateTime.FromText(dateText, [Format="ddMMMyyyy", Culture="en-US"]), result = if not tryFormat1[HasError] then tryFormat1[Value] else if not tryFormat2[HasError] then tryFormat2[Value] else if not tryFormat3[HasError] then tryFormat3[Value] else null in result ), // Ensure the ParsedDate column is of type date #"Changed Type1" = Table.TransformColumnTypes(#"Added ParsedDate",{{"ParsedDate", type date}}) in #"Changed Type1" Output: Hope this helps!! If this solved your problem, please accept it as a solution and a kudos!! Best Regards,Shahariar Hafiz

ajitsahoo8338

Helper III

1 year ago

Solved

Need help on one logic

This is my sample column Table.
As you can see correction notes start from a date. I want to extract that date from each row. It may be in MM/DD/YYYY or MM/DD/YY or DDMMMYYYY format. How is it achievable?

Project Name	Correction Notes
LTR	1/13/25 - parts added/reserved to SO 134931. JWG 11/6/24: controlnet connectors are called out in the interconnect box. i think the RJ45 connectors should follow suit to keep consistency. Need to remove item 55 from BOM and add RJ45 connector to 211177. -GJHOLMES
PGR	1/9/2025: Redline Released -GJHOLMES 11/6/24: Items 51-56 see to be added under REV D as a designated conduit run for the vision system. This system was originally COAX but has now changed to Ethernet
GPR	01jan2025 This system was originally COAX but has now changed to Ethernet

Thank You,
Ajit

Need Help

Show and Tell

Forum Discussion

Need help on one logic

Related content

Logic problem?

FX Conversion Logic

Slicer by logic conditions

Managing logical data unbundling between companies

Share % Measure Logic tweak help