Sunday, August 29, 2021

How to extract a String based on the Delimiter using M-Query in Power Query

How to extract Sub String from a String based on Delimiter in Power BI
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 :

The M-Query behind each Custom Column is 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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog