Forum Discussion
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 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
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 addDatePete
10 Replies
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 
ronrsnfldSuper User
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"
- 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
