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

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.