Forum Discussion

Patrykrz's avatar
Patrykrz
Frequent Visitor
3 years ago
Solved

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

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 i...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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's avatar
Patrykrz
Frequent Visitor
3 years ago

 

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 [email protected] 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 [email protected] ** 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
ronrsnfld's avatar
ronrsnfld
Super User
3 years ago

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's avatar
    Patrykrz
    Frequent Visitor
    3 years ago

    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's avatar
      ronrsnfld
      Super User
      3 years ago

      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.