Issue with date extract from text string- Power Query, Power BI

[Patrykrz]

Hello,

I am running a report where columns contain lots of various data- text and numeric. From these text fields I would like to extract the date which is always written as "yyyy-MM-dd". There is no common pattern- the date can be in the beginning of the text field, or randomly in any place. It can be after space (or no space) or any other character. There can also be few dates in the same column, but i want to extract the very first one in the text string.

Note: I have tried with column from example and it does not cover 100% scenarios, so i am looking for a formula. In excel there is no issue: =DATEVALUE(MID([@Kolumna 1],SEARCH("20??-??-??",[@Kolumna 1]),10)) and it works. It's just I can reflect the same in Power Query for Power Bi report.

example of the column below, but there ale few thousand records in reality:

Id greatly appreciate help

[AlienSx]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVVRbttGEL3KwO2HrYg0SUmm8hdYloLCdiwQSN3CzccCC2jQDAEa2FlX/eodcoKepTfpSfpml5IToWgBQ6DJ2Zk3b968fXo6C47ZsTriYXASWSh6Jnupg3gOxBLoTgIiJpPXYFUKQkXV0Av7qJ6c56g41yvJC0Imk259TU3VzIpqXjRz6gJbkh+YY8AJ+qxDmNItXtLDGFcXTfW/cU1RzYq6oo04fL3nX1Wd0p0GO+WUQ3ma4fy9kIrEz+ov6DxuL8Y0TdEsCN9Y1AXq1KEHYocnTytBJrcvowhdqzv7NH06AznBmh9YOLXfKXMQR/fqmLyjN23bLvE3W7TL19ZbWv82eGccKTrRnr0koJYiorT0SHqryvQ4IrtCj7T+NlCYvevp+4oU9Y1sVXxh3dsPKACA3lsPO7LZuWBP7KSnjWppiUvq4iuFC7pmQyVM0hbdfaSHm2bZXk8Jlcgzi+s5eHw2YXiPUNSK4oFoihKogFdVos9ZFuADpkTVLQMH04r/+jPq7wiBbN6xe1bfSynOcCHce0vPqbmAYYFZms+XRbN8iwlXmHHclVS385HMq6Ke0+Yj0qHQsHPslZ4V2jyytBIHjI9eIN3eWZVbTaMRcHV5eWAxt5KDA2Y4ZnnhrKATWS6KWUUfT+o9q4nmUBdVSorRJlxCkzsIqSvH4zNT/+onQ2G7NB614UjiKke1RT2jn7txDdVjVjkQWO/Uie5/0apqWiD0OzaBQwAAhNbuStqyihF+yFW1luvFByZE9viUBukCW8LT+KuiaahDWlPDV+EjAlu1HriBKgPL+3WM5N2/JKyhCHQcV0CBHYJfOAwF2nJYQ8yek4RN2Ix2hROpvkc9jMdGcAIkgxhOGUN+VEmOJVqSdJvsAQeQ38ykG8VfF/WSrgGrSPWZd4Bkjx19R/ViPl+MCkA4Ymuzuc7+D/tBjwfSanWy3mKrZN1ZgvXl2jR3XGq4JTTtbMuwoyTSfcCDg12AC/r7jy8pGT2WY6WqaK5o83DTrX68sa8uzwRv6PxRbWfYJsTugiJY8sbUcTBTem+GkX7GdImtyWSrLu1zhtwn/OlolH5g3lPTZsux9LOqNW9ErAK7T/5g4JO1O+19CNl90OWeetvcyeQovLqBKeoLamXRxOjtQikPm5fnievD7MT2GBWc+Udm1VSP49nDMLbbYys2MmNm/UEK+k828CNmtAciKtvg5i22yHxAMI1UNTnfSNDB2bM3HyAZvNSf7MHEPqW3JY4xXQ54BdHruxWY2ogPmowNXHQwu6N1VEvaovUT1Xho3mYCzeArVshrb8oZTLLWC9XZT7KK7Q48wPG4cYwfm8nBBHH3CiRQprv7WHlxtCd1A7rCOk3zRLyipVFcsff1iCoo1GD54F2vaeZ2GRKuwE//AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    find_date = (t as text) =>
        List.Generate(
            () => [txt = t, pos = 1, delim = "", d = null, found = false],
            (x) => not x[found],
            (x) => 
                [txt = try Text.AfterDelimiter(x[delim]) otherwise x[txt],
                pos = Text.PositionOf(txt, "20"),
                delim = Text.Middle(txt, pos, 10),
                d = try Date.FromText(delim, [Format = "yyyy-MM-dd"]) otherwise null,
                found = if (x[d] <> null) or (x[pos] = -1) then true else false],
            (x) => x[d]
        ),
    res = Table.AddColumn(Source, "Date", each List.Last(find_date([Column1])))
in
    res

[BA_Pete]

Hi @Patrykrz ,

Can you provide 5 or 10 of your examples in a copyable format please?

Thanks,

Pete

[Patrykrz]

Hello Pete, 

I hope it works, please see below. I have changed the letter codes to cover the real text.

COMMENTS

tahhahua hppalvhl vdh ahhaupldht hlt Lltua **tahhahua huu tl -02 whdvud adhvu ahmu lwhua**REB 2023-04-24 Rthtua Ihhvtdvu kupt, Khtu O 2023-01-20 Rthtua Ihhvtdvu kupt, Khtu O 2022-03-10 Flavu Mhjuuau Luttua auht.Rthtua Ihhvtdvu (Gl ullvkud) (vP) 2022-02-25 Gl hluat Ruaadh ha Rud Cluhtay.vll Bua

vhlthua phlhu tl Ruhhtla Muah da +77787783578 2023-04-27 Expdaud aut, umhdl auht tl vuatlmua Kuuh W 2022-06-03 Emhdl auht tl vlhhdam $0 uhlhhvu uut thuy thuau da mdaadhg auplatdhg halm Fuu.2022. Rv 2022-03-15 Bhaud lh l7-RMv OD287B, hhd dhhlamhtdlh palvddud uy vlduht, aulatud 0 hluaa lh hda uu

Khadhh Chávuz hlhah@haqudml.lag ha hdddtdlhhl vlhthvt ph 448-289-1000 Fvg. 174 2023-06-14 FU hla upgahdu qultu auht tl Clauy Wdlldhma ha Kuh da lut // vlhhdamud uy Clauy thht qultu wha auht, Khtu O 2023-05-30 Upgahdu qultu auquuat auht tl Kuh. vv aut. Mhgdh R. 2023-03-24 CX da hlt auquuatdhg l

2023-07-13 YR hppalvud. Ruquuatud Lualuy'a adghhtuau uhdua lL. Phuldhh 2023-07-07 YR wdth tuamdhhtdlh athatud. Phuldhh 2023-06-22 Rdghud luamdhhtdlh Ruquuat Flam auvudvud. Rthtua luamdhhtdhg. Phuldhh 2023-06-21 lhda vC wdll uu alld, hakud hdhhhvu tl vhlvulhtu audmuuaamuht, luamdhhtdlh Ruq Flam p

2023-07-13 vC da dh ahlu. lRF auht. Rthtua vv aut. MhgdhR 2022-01-18 Bdll-tl vhhhgud tl vR # 15445, Khtu 2021-11-02 Rhtu typu vhhhgud halm RlEP RvlER tl E/E ha vuatlmua hha phaaud thu llRN thauahlld – vhhh W. 2021-10-26 FODRCVD– adghud FOD (Wuha hhd luha) vmuhdmuht auvudvud, Ghdl Ghdl 2021-10

**Pua dhhl halm mhhhgumuht vlmphhy 2706-03 hhd 3072-02 hau phdd lh thu ahmu aumdtthhvu uvuay mlhth** 2023-07-12Ruhuwhl Flam avvd vdh. vlhhdamhtdlh huudud hla hddauaa vhhhgu. Ruhuwdhg aut. Kl 2021-10-18 FODRENl-  (Wuha hhd luha) vmuhdmuht auht lut, Ghdl 2020-05-29 Luda llaaua hddud lh

**vhlthua umhdl hddauaa hla mlhthly phymuht da hvvluhtaphyhulu@ChauFldtu.lag ** Rhah 2023-05-08 Plhh vhhhgud halm Rdlvua dhtl Plhtdhdum ha pua vmuhd 1. vv athtua kupt hla mlhdtladhg thu upgahdu dhvldvu. vdh 2023-05-05 auht tl uap hdmdh, avvd duly adghud vmd1 halm Rtuphhhdu R.  2023-05-04 Clu a

[BA_Pete · Accepted solution]

Hi @Patrykrz ,

Try the following code to get this output:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVVRbttGEL3KwO2HrYg0SUmm8hdYloLCdiwQSN3CzccCC2jQDAEa2FlX/eodcoKepTfpSfpml5IToWgBQ6DJ2Zk3b968fXo6C47ZsTriYXASWSh6Jnupg3gOxBLoTgIiJpPXYFUKQkXV0Av7qJ6c56g41yvJC0Imk259TU3VzIpqXjRz6gJbkh+YY8AJ+qxDmNItXtLDGFcXTfW/cU1RzYq6oo04fL3nX1Wd0p0GO+WUQ3ma4fy9kIrEz+ov6DxuL8Y0TdEsCN9Y1AXq1KEHYocnTytBJrcvowhdqzv7NH06AznBmh9YOLXfKXMQR/fqmLyjN23bLvE3W7TL19ZbWv82eGccKTrRnr0koJYiorT0SHqryvQ4IrtCj7T+NlCYvevp+4oU9Y1sVXxh3dsPKACA3lsPO7LZuWBP7KSnjWppiUvq4iuFC7pmQyVM0hbdfaSHm2bZXk8Jlcgzi+s5eHw2YXiPUNSK4oFoihKogFdVos9ZFuADpkTVLQMH04r/+jPq7wiBbN6xe1bfSynOcCHce0vPqbmAYYFZms+XRbN8iwlXmHHclVS385HMq6Ke0+Yj0qHQsHPslZ4V2jyytBIHjI9eIN3eWZVbTaMRcHV5eWAxt5KDA2Y4ZnnhrKATWS6KWUUfT+o9q4nmUBdVSorRJlxCkzsIqSvH4zNT/+onQ2G7NB614UjiKke1RT2jn7txDdVjVjkQWO/Uie5/0apqWiD0OzaBQwAAhNbuStqyihF+yFW1luvFByZE9viUBukCW8LT+KuiaahDWlPDV+EjAlu1HriBKgPL+3WM5N2/JKyhCHQcV0CBHYJfOAwF2nJYQ8yek4RN2Ix2hROpvkc9jMdGcAIkgxhOGUN+VEmOJVqSdJvsAQeQ38ykG8VfF/WSrgGrSPWZd4Bkjx19R/ViPl+MCkA4Ymuzuc7+D/tBjwfSanWy3mKrZN1ZgvXl2jR3XGq4JTTtbMuwoyTSfcCDg12AC/r7jy8pGT2WY6WqaK5o83DTrX68sa8uzwRv6PxRbWfYJsTugiJY8sbUcTBTem+GkX7GdImtyWSrLu1zhtwn/OlolH5g3lPTZsux9LOqNW9ErAK7T/5g4JO1O+19CNl90OWeetvcyeQovLqBKeoLamXRxOjtQikPm5fnievD7MT2GBWc+Udm1VSP49nDMLbbYys2MmNm/UEK+k828CNmtAciKtvg5i22yHxAMI1UNTnfSNDB2bM3HyAZvNSf7MHEPqW3JY4xXQ54BdHruxWY2ogPmowNXHQwu6N1VEvaovUT1Xho3mYCzeArVshrb8oZTLLWC9XZT7KK7Q48wPG4cYwfm8nBBHH3CiRQprv7WHlxtCd1A7rCOk3zRLyipVFcsff1iCoo1GD54F2vaeZ2GRKuwE//AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

// Relevant steps ---->
    addDateCode =
        Table.AddColumn(
            Source,
            "dateCode",
            each List.Select(
                Text.SplitAny(
                    Text.Replace([Column1], "-", "0"),
                    Text.Remove([Column1],{"0".."9"})
                ),
                each Text.Length(_) = 10 and Text.StartsWith(_, "20")
            ){0}
        ),
    addDate =
        Table.AddColumn(
            addDateCode,
            "date",
            each Date.From(
                Text.Combine(
                    {
                        Text.End([dateCode], 2),
                        Text.Range([dateCode], 5, 2),
                        Text.Start([dateCode], 4)
                    },
                    "-"
                )
            )
        )
        
in
    addDate

Pete

[Patrykrz]

Hello,

This might be a stupid question, but what part shall I paste into advanced editor to make a connection for this formula to my real live SQL database 😞 

Currently it looks like that (original table and server name replaced):

let
Source = Sql.Databases("SERVERNAMEABC"),
TABLENAME = SOURCE {[Name="TABLENAME"]}[Data],
dbo_XYZ = ESP{[Schema="dbo",Item="XYZ"]}[Data],
#"Merged columns" = Table.CombineColumns(#"Usunięto kolumny",{"AGREEMENT", "SUFFIX"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"ID"),
#"Changed type" = Table.TransformColumnTypes(#"Scalono kolumny",{{"EXPIRYDATE", type date}}),

in

#"Changed type"

I get token error unfortunately when I try to paste the code

[BA_Pete]

The full code is just an example so you can see the steps taken in a standalone query.

To implement in your query, just add two new custom columns:

First column - call this 'dateCode'.

You will need to change [Column1] references to match whatever the name of your text column is:

List.Select(
    Text.SplitAny(
        Text.Replace([Column1], "-", "0"),
        Text.Remove([Column1], {"0".."9"})
    ),
    each Text.Length(_) = 10 and Text.StartsWith(_, "20")
){0}

Second column:

Date.From(
    Text.Combine(
        {
            Text.End([dateCode], 2),
            Text.Range([dateCode], 5, 2),
            Text.Start([dateCode], 4)
        },
        "-"
    )
)

Pete

[Patrykrz]

Thank you Pete and thank you all! 🙂 you saved my day

[ronrsnfld]

Since yyyy-mm-dd should be universally understood as a date, you can try the following:

Code for Custom Column

=List.RemoveNulls(List.Transform(Text.Split([Column1]," "), each try Date.From(_) otherwise null)){0}

Full Code in Advanced Editor to Reproduce

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVVRbttGEL3KwO2HrYg0SUmm8hdYloLCdiwQSN3CzccCC2jQDAEa2FlX/eodcoKepTfpSfpml5IToWgBQ6DJ2Zk3b968fXo6C47ZsTriYXASWSh6Jnupg3gOxBLoTgIiJpPXYFUKQkXV0Av7qJ6c56g41yvJC0Imk259TU3VzIpqXjRz6gJbkh+YY8AJ+qxDmNItXtLDGFcXTfW/cU1RzYq6oo04fL3nX1Wd0p0GO+WUQ3ma4fy9kIrEz+ov6DxuL8Y0TdEsCN9Y1AXq1KEHYocnTytBJrcvowhdqzv7NH06AznBmh9YOLXfKXMQR/fqmLyjN23bLvE3W7TL19ZbWv82eGccKTrRnr0koJYiorT0SHqryvQ4IrtCj7T+NlCYvevp+4oU9Y1sVXxh3dsPKACA3lsPO7LZuWBP7KSnjWppiUvq4iuFC7pmQyVM0hbdfaSHm2bZXk8Jlcgzi+s5eHw2YXiPUNSK4oFoihKogFdVos9ZFuADpkTVLQMH04r/+jPq7wiBbN6xe1bfSynOcCHce0vPqbmAYYFZms+XRbN8iwlXmHHclVS385HMq6Ke0+Yj0qHQsHPslZ4V2jyytBIHjI9eIN3eWZVbTaMRcHV5eWAxt5KDA2Y4ZnnhrKATWS6KWUUfT+o9q4nmUBdVSorRJlxCkzsIqSvH4zNT/+onQ2G7NB614UjiKke1RT2jn7txDdVjVjkQWO/Uie5/0apqWiD0OzaBQwAAhNbuStqyihF+yFW1luvFByZE9viUBukCW8LT+KuiaahDWlPDV+EjAlu1HriBKgPL+3WM5N2/JKyhCHQcV0CBHYJfOAwF2nJYQ8yek4RN2Ix2hROpvkc9jMdGcAIkgxhOGUN+VEmOJVqSdJvsAQeQ38ykG8VfF/WSrgGrSPWZd4Bkjx19R/ViPl+MCkA4Ymuzuc7+D/tBjwfSanWy3mKrZN1ZgvXl2jR3XGq4JTTtbMuwoyTSfcCDg12AC/r7jy8pGT2WY6WqaK5o83DTrX68sa8uzwRv6PxRbWfYJsTugiJY8sbUcTBTem+GkX7GdImtyWSrLu1zhtwn/OlolH5g3lPTZsux9LOqNW9ErAK7T/5g4JO1O+19CNl90OWeetvcyeQovLqBKeoLamXRxOjtQikPm5fnievD7MT2GBWc+Udm1VSP49nDMLbbYys2MmNm/UEK+k828CNmtAciKtvg5i22yHxAMI1UNTnfSNDB2bM3HyAZvNSf7MHEPqW3JY4xXQ54BdHruxWY2ogPmowNXHQwu6N1VEvaovUT1Xho3mYCzeArVshrb8oZTLLWC9XZT7KK7Q48wPG4cYwfm8nBBHH3CiRQprv7WHlxtCd1A7rCOk3zRLyipVFcsff1iCoo1GD54F2vaeZ2GRKuwE//AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
   
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each 
        List.RemoveNulls(
            List.Transform(
                Text.Split([Column1]," "), 
                    each try Date.From(_) otherwise null)){0},
         type date)
in
    #"Added Custom"

[Patrykrz]

Hello!

Thank you very much for the time.

It's so simple in excel, but it seems like a super difficult thing in Power BI 😞 Unfortunately your code won't work for two reasons:

1) part of the text contains internal numbering "XXXX-XX". Power BI treats it as a date which is not right (see the screen below)

2) in some cases it takes the wrong date:

examples:

2018-02-19 luamdhhtud ha pua auplat halm lEXlRON RC 2012-06-28 Rudhathtud ha pua umhdl auquuat halm Chhd Gadhhdth.Rthtua vhhhgud halm luamdhhtud tl vvtdvu RC 2012-05-30 luamdhhtud ha pua Fuu 2012 Cuaahh Ruplat. (RD)

2013-12-10 Rudhathtud ha pua Nlv 2013 Cuaahh Ehalll Ruplat. Rthtua vhhhgud halm luamdhhtud tl vvtdvu RC 2013-11-26 luamdhhtud ha pua Cul 2013 Cuaahh Bdlldh Ruplat. Rthtua vhhhgud halm vvt tl luamdhhtud RC

[ronrsnfld]

You can use Regular Expressions to extract only values that have that format, and then convert it to a "real date".

In Power BI Desktop you can also implement Regular Expressions in Python or R. I chose to use a Java implementation as that can be used in either Power BI or Excel versions of Power Query. But the principle would be the same.

Add the following as a Blank Query and rename it fnRegexExtr:

//Rename fnRegexExtr

//see http://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-in-excel-with-java-script/
// and https://gist.github.com/Hugoberry/4948d96b45d6799c47b4b9fa1b08eadf

let   fx=(text,regex)=>
    Web.Page(
        "<script>
            var x='"&text&"';
            var y=new RegExp('"&regex&"','g');
            var b=x.match(y);
            document.write(b);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx

You can then use it in your Main Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY9LqwIxDEb/SpiVgpVOB0W3tz52Cl1dkFkEAmaRAS80/f2mA75gEO6ykHO+08ulCb7dOB9cuwVRHIg5KwEj3BQB9SaY7SUDyP5X0vkEKYIxwfm1CxtISoz5ndGBSYz8U32gkZngiOamzMtUzxEKM19HrMpf01mglExFX0sr1/mJuoPqeABREZmtpdYuYZZ286ZfjH/rnAlaP9F5klLp7kHvLUTkKflnpe20LqwnKqPKx84PiRB/3TFzHXhzpdj0/R0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.From(Text.Split(fnRegexExtr([Column1], "\\b\\d{4}-\\d{2}-\\d{2}\\b"),","){0}))
in
    #"Added Custom"

Note that the results are being returned as "real dates" in your local culture (eng-US for me). If you prefer the return as the "yyyy-mm-dd" string, then don't use the "Date.From" function.