Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a free form field that has dates and currency. Sometimes the dates are mm/dd/yy or month name year, i.e., April 2024. The currency does not have decimals but may or may not have a dollar sign $.
I would like to extract the numbers and add a delimiter between them. For example, the following line:
"The FRC was updated on 4/14/2006.Verified via Dan Daily that this building exists. Note this chapel/six post is where the old library used to be and now the library is part of another building.Old FRC was 964,600"
I found where I could use the Text.Select and Table.AddColumn in another post to produce this result:
4142006964600 |
What I would like is to have a delimeter between the date 4142006 and the dollar amount 964,600. Ideally, I would like to keep the date format and the currency format and have a delimeter between the two such as: 4/14/2006#964,600.
Thank you.
Solved! Go to Solution.
Hi @samye228, this could help you for such 2 cases, but you will probably need some update for other situations.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1Y/BCsJADER/ZfAsbZVS8CiKRwURL+IhdaMNLLvL7tbq3xsVvfkBHpLDvEmGORxGu46x2i4wUEIfDGU28A51OanLaVU1xZ6jnEXVqxCW5HTE3pE7yrokoe3FGnEX8E1STgXWPvMbnToKbMskNwSfMlQaOo5PyvDWwEobKd7RJw3IHi2DnIHzw8vywXoXKGb4s2KvJH5Ti42++RSYNfW4qarRcfyz2DxEsf9d7vgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_Extracted = Table.AddColumn(Source, "Extracted", each
[ a = {"0".."9", ",", "/"},
b = Text.SplitAny([Column1], " ."),
c = List.Transform(b, (x)=>
if (try Number.From(x, "en-US") otherwise false) is number then x else
if (try Date.FromText(x, [Format="M/d/yyyy", Culture="en-US"]) otherwise false) is date then x else
if (try Date.FromText(x, [Format="MMMM/d/yyyy", Culture="en-US"]) otherwise false) is date then Date.ToText(Date.FromText(x, [Format="MMMM/d/yyyy", Culture="en-US"]), [Format="M/d/yyyy", Culture="en-US"]) else false ),
d = List.Select(c, (x)=> x <> false),
e = Text.Combine(d, "#")
][e], type text)
in
Ad_Extracted
Hi @samye228, this could help you for such 2 cases, but you will probably need some update for other situations.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1Y/BCsJADER/ZfAsbZVS8CiKRwURL+IhdaMNLLvL7tbq3xsVvfkBHpLDvEmGORxGu46x2i4wUEIfDGU28A51OanLaVU1xZ6jnEXVqxCW5HTE3pE7yrokoe3FGnEX8E1STgXWPvMbnToKbMskNwSfMlQaOo5PyvDWwEobKd7RJw3IHi2DnIHzw8vywXoXKGb4s2KvJH5Ti42++RSYNfW4qarRcfyz2DxEsf9d7vgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_Extracted = Table.AddColumn(Source, "Extracted", each
[ a = {"0".."9", ",", "/"},
b = Text.SplitAny([Column1], " ."),
c = List.Transform(b, (x)=>
if (try Number.From(x, "en-US") otherwise false) is number then x else
if (try Date.FromText(x, [Format="M/d/yyyy", Culture="en-US"]) otherwise false) is date then x else
if (try Date.FromText(x, [Format="MMMM/d/yyyy", Culture="en-US"]) otherwise false) is date then Date.ToText(Date.FromText(x, [Format="MMMM/d/yyyy", Culture="en-US"]), [Format="M/d/yyyy", Culture="en-US"]) else false ),
d = List.Select(c, (x)=> x <> false),
e = Text.Combine(d, "#")
][e], type text)
in
Ad_Extracted
Impossible(?) to do this as it's free form data entry.
How do we know if the numbers entered are a date or currency?
How do we know which order those values appear in the data?
How do we know if there are more than one occurence of each type of data?
Sorry, with free form entry this type of problem isn't doable. The only way you might do it would be if the date is always the same format (same delimiters, always use 2 digits for month and day and 4 digits for year). You could then be able to distinguish dates from currency. But as I said, if there is more than one date and/or more than one curency values, it gets very complicated if not impossible.
You'd be better off redesigning the data entry to control the way the data is entered.
Phil
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.