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

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi Patrykrz ,

 

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

 

Thanks,

 

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

         

         

         

    • BA_Pete's avatar
      BA_Pete
      Super User
      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,

         

        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