Suppose we have the Table with File names as shown below :
Now we can to create the Custom columns based on the Delimiter of the File Name as follows :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydghOzEktjvcvSkktKo43MjAyNDAEU3oGZnoVOcUVSrE6uBSaQxQaGiEURkRGYSg0MDDUBVEgE5OLy/CoM4eoAxoIVhcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FileName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FileName", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "File_Name_Start", each Text.Start([FileName],3)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "File_Extension", each Text.End([FileName],4)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "File_Period", each (Text.AfterDelimiter([FileName],"_",1))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "File_Year", each Number.FromText(Text.Start(Text.AfterDelimiter([FileName],"_",1),4))),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "File_Month_Start", each Text.From(Text.Start(Text.AfterDelimiter([FileName],"_",1),4)) & "-" & Text.From(Text.End(Text.Start(Text.AfterDelimiter([FileName],"_",1),6),2))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "File_Month_End", each Text.Start(Text.End(Text.BeforeDelimiter([FileName],".",1),7),4) &"-"&Text.End(Text.BeforeDelimiter([FileName],".",1),2))
in
#"Added Custom5"
Thanks, Tamatam
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.