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
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

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

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

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