Forum Discussion

ajitsahoo8338's avatar
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 achi...
Need Help
Show and Tell
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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

danextian's avatar
danextian
Super User
1 year ago

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.