Need help on one logic

[ajitsahoo8338]

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

[danextian]

Hi @ajitsahoo8338 

Try the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZLdTgIxEIVf5WSvZdcuLEbv/CEoATFo1IR4UbYDW1la0nZR3sZn8cmcglHjrRemF/2bOZ35TqfTZHg3SQ4SkYl2lhdoYS1d8JBKkcoceXIbUggWt2OIdue4LdL3Nwwe+u9vPAuRdbO8c4LSmuBsbSjEpaEyWMcqjlDKumYF2wRog1ART4HcZxRm9jWF5nNtlrvbyaBT/NTwlW1qhbmta/sC3+gQq1kSrWOU1z6QKbcprmlfp6OV3fAjgVYoCsydXeFsPII0Knb1Sz9m5EKIo6MUrf7gcjwc9W6Tp4NpctPfcznO8sO8OMGEVK0N10c1Sc9vfYVHEv8zvvlfcbsehWgVXXii2NaM9i6iMYocJr17XECyKVDk9cLIwHcMQkWkrjGM2O0c2GivrYHfMtpVirtK+88NXjjfOr3Qhl3d4nx8+ogZO1vxuWF7ykqaxd6HHks5/g87mP2bCPNQPEsTaf5d8+kD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Correction Notes" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Correction Notes", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Split", each Text.Split([Correction Notes]," ")),
    #"Expanded Split" = Table.ExpandListColumn(#"Added Custom", "Split"),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded Split",{{"Split", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Split", Text.Clean, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text",":","",Replacer.ReplaceText,{"Split"}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Value", "En-GB", each try Date.From([Split], "en-gb") otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "En-US", each try Date.From([Split], "en-us") otherwise null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Filter", each Text.From([#"En-US"])??Text.From([#"En-GB"] )= null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each [Filter] <> true),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Filter"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"En-GB", type date}, {"En-US", type date}})
in
    #"Changed Type1"

Please note that without input control, there's only so much Power Query can do for you.

[girishthimmaiah]

You can extract the date from the "Correction Notes" column in Power BI using DAX:

ExtractedDate =
VAR TextValue = 'Table'[Correction Notes]

-- Find the first occurrence of a date format
VAR Date1 = MID(TextValue, SEARCH("/", TextValue, 1, 0) - 2, 10) -- MM/DD/YYYY or MM/DD/YY
VAR Date2 = MID(TextValue, SEARCH("jan", LOWER(TextValue) & "jan", 1, 0) - 2, 9) -- DDMMMYYYY format

-- Return the first found date
RETURN IF(NOT(ISERROR(Date1)), Date1, Date2)

[shafiz_p · Accepted solution]

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]

Thank You so much for your help. It works.

Warm Regard,
Ajit Sahoo

[techies]

Hi @ajitsahoo8338 have you given this a try?

Add Column > Column from Examples > From Selection. In the new column, manually type the date from the first row as an example (e.g., 1/13/25), same for other rows (e.g., 01Jan2025), and Power Query will recognize the pattern and extract the dates automatically.

[SamanthaPuaXY]

@ajitsahoo8338 , assuming the correction notes is a column of your table. You'll need to
1) split text by de delimiter(by space/-/:). It seems like you need to do multiple step to clean it properly
2) Change splitted text into date format. While it can be change from DDMMYYYY or MMDDYYYY, there needs to be a consistency on what it should be changed from or you could identify a logic to determine if its meant to be ddmmyyyy or mmddyyyy. 

Should there be no possible logic, you could try and catch error -> if they don't fall in DDMMYYYY convert as MMDDYYYY. Hope this helps