let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
#"Split Column by Position" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.2", Splitter.SplitTextByRepeatedLengths(2), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type text}, {"Column1.2.1", Int64.Type}, {"Column1.2.2", Int64.Type}, {"Column1.2.3", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}}, "en-US"),{"Column1.2.1", "Column1.2.2", "Column1.2.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"New Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"New Date", type date}})
in
#"Changed Type2"
A | B | |
1 | Column1.1 | New Date |
2 | Rooney | 5/10/2046 |
3 | Rav | 3/9/2016 |
4 | Sameerthegreat | 12/8/1999 |
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
, MaxYr = Date.Year(Date.From(DateTime.LocalNow()))
, GetName = Table.AddColumn(Source, "Name"
, each Text.Remove([Column1], {"0".."9"}))
, GetNumbers = Table.AddColumn(GetName, "Numbers"
, each Text.Select([Column1], {"0".."9"}))
, NumbersAsDate = Table.AddColumn(GetNumbers, "TempDate", each Text.Format("#{0}/#{1}/#{2}"
, {
Text.Range([Numbers], 0, 2)
, Text.Range([Numbers], 2, 2)
, Text.Range([Numbers], 4, 2)
}
))
, ChangeWithLocale = Table.TransformColumnTypes(NumbersAsDate, {{"TempDate", type date}}, "en-BE")
, GetDate = Table.AddColumn(ChangeWithLocale, "Date"
, each if Date.Year([TempDate]) > MaxYr
then Date.AddYears([TempDate], -100)
else [TempDate], Date.Type
)
, KeepNameDate = Table.RemoveColumns(GetDate,{"Column1", "Numbers", "TempDate"})
in
KeepNameDate
Thanks you very much its work as per your instruction.Here is a Power Query Solution
Code:let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}), #"Split Column by Position" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.2", Splitter.SplitTextByRepeatedLengths(2), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type text}, {"Column1.2.1", Int64.Type}, {"Column1.2.2", Int64.Type}, {"Column1.2.3", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}}, "en-US"),{"Column1.2.1", "Column1.2.2", "Column1.2.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"New Date"), #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"New Date", type date}}) in #"Changed Type2"
Data Range
A B 1 Column1.1 New Date 2 Rooney 5/10/2046 3 Rav 3/9/2016 4 Sameerthegreat 12/8/1999