Custom Function to Create a Table from a Text String

[danextian]

A few days ago, I was pulling data from a Salesforce object related to quote party. The data though was coming from a different system and was forwarded to Salesforce as concatenated text strings. I used Text.BetweenDelimiters function to extract the quote party information as there was only a few that needed to be extracted.  But I thought, what if I had to do the same with about 30, 40 or more fields? Creating at least 30  or 40 additional steps in Power Query would be very tedious...so I decided to follow a more dynamic approach.

Here's my sample query using a dummy data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRCkgsKqn0S8xNVVFWcUnMA1HB+TkpIflAhqGRsYmpGZDhnF+aV1JUCWSF5mWWpKYoBJcklqQWg9SCGEDaL7VcITK/KFspVidaKSU1DWgy3BgzcwtLAxRTnBPzElMSkbT7JgLNzU9KBGvPyMwiUzuQGZVZADYkOyeX+r4rhlgAJC0NjAwNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Transaction Number" = _t, String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Number", type text}, {"String", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Delimiter Occurence", each ( (Text.Length([String]) - Text.Length( Text.Replace([String], "$#$", "") ) )/Text.Length("$#$") ) + 1, Int64.Type),
    MaxOccurence = List.Max(#"Added Custom"[Delimiter Occurence]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "String", Splitter.SplitTextByDelimiter("$#$"),
MaxOccurence),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"String.1", type text}, {"String.2", type text}, {"String.3", type text}, {"String.4", type text}, {"String.5", type text}, {"String.6", type text}, {"String.7", type text}, {"String.8", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Delimiter Occurence"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "ListWithValues", each List.Select(List.Skip(Record.FieldValues(_)), each _ <> null)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ListWithPositions", each List.Select(List.Positions([ListWithValues]), each Number.IsEven(_))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Values", each List.Alternate([ListWithValues],1,1)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Fields", each List.RemoveMatchingItems([ListWithValues],[Values])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Table From String", each Table.PromoteHeaders(Table.Transpose(Table.FromColumns({[Fields],[Values]})), [PromoteAllHeaders = true])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Transaction Number", "Table From String"}),
    #"Expanded Table From String" = Table.ExpandTableColumn(#"Removed Other Columns", "Table From String", {"PartyName", "SoldTo", "Country", "State"}, {"PartyName", "SoldTo", "Country", "State"})
in
    #"Expanded Table From String"

I modified the query and created this custom function I call fnTableFromString. 

let func =     
    (MyString as text, Del as text, ToText as any) as table =>


let

    //splits text by delimiter
    TextSplit = Text.Split(MyString, Del),
    
    //returns values from text string assuming the values start from position 1 with interval of  2
    ValuesList = List.Alternate(TextSplit,1,1),

    //returns field names from text string, those not found in ListValues are field names
    FieldsList = List.RemoveMatchingItems(TextSplit, ValuesList),
    
    //creates a table from ListValues and ListFields
    TableFromLists = Table.FromColumns({FieldsList, ValuesList}),

    //transposes the created table
    TransposedTable = Table.Transpose(TableFromLists),

    //promotes first row to headers
    PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars = true]),

    //converts column types to text
    DataTypesToText = Table.TransformColumnTypes( PromotedHeaders, List.Transform(Table.ColumnNames(PromotedHeaders), each {_, type text}) ),

    
    //converts to text?
    Converted = if ToText = null then PromotedHeaders else DataTypesToText 
    
  
in
   DataTypesToText, 


    //meta-record for documentation purposes
    documentation = [
    Documentation.Name =  "Table From String", 
    Documentation.Description = "Splits <code>MyString </code> into columns per each occurence of <code>Del</code>. The generated columns are converrted to text if
                                    <code>ToText</code> is not null (this applies to primitive values only as the columns from the table generated when this function is invoked
                                     are by default of data type any.",
    Documentation.LongDescription = "Splits <code>MyString </code> into columns per each occurence of <code>Del</code>. The generated columns are converrted to text if
                                    <code>ToText</code> is not null (this applies to primitive values only as the columns from the table generated when this function is invoked
                                     are by default of data type any.",
    Documentation.Source = "https://community.powerbi.com/t5/user/viewprofilepage/user-id/15697",
    Documentation.Author = "Dane Belarmino",
    Documentation.Examples = {
            [Description =  "To reach out to the author, please go to https://community.powerbi.com/t5/user/viewprofilepage/user-id/15697.", 
            Code  = "(""PartyName$#$Dane$#$SoldTo$#$123456$#$Country$#$United States$#$State$#$New York"", ""$#$"", null)",
            Result= "#table({""PartyName"",""SoldTo"",""Country"",""State""},{{""Dane"",""123456"",""United States"",""New York""}})" 
            // 
            ]
            }
            ]       
     
in 
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

The custom function above assumes that there is only one delimiter. If there are two or more, you'll probably have to transform your string first to contain just one delimiter prior to applying this function or use Table.SplitColumn and use Splitter.SplitTextByEachDelimiter as the splitter function. 

The custom function above also assumes that each  string will be convert to just one-row table. If there are more than one row, the original string will have to be split first into rows prior to applying this function. This can be achieved by creating a custom formula similar to this:

List.Transform( Text.Split( [ColumnName to split], delimiter),  each fnTableFromString(_, Del, ToText ) ) 

which will create a list containing one or more tables like the result in the query below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRCkgsKqn0S8xNVVFWcUnMA1HB+TkpIflAhqGRsYmpGZDhnF+aV1JUCWSF5mWWpKYoBJcklqQWg9SCGEDaL7VcITK/KFspVidaKSU1DWgy3BgzcwtLAxRTnBPzElMSkbT7JgLNzU9KBGvPyMwiUzuQGZVZADYkOyeX+r4rhlgAJC0NjAwNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Transaction Number" = _t, String = _t]),
    Del = "$#$",
    DelLength = Text.Length("$#$"),
    AddedLine = Table.TransformColumns(Source,{{"String", each _ & "@@"  & _, type text }}),
    #"Added Custom" = Table.AddColumn(AddedLine, "Custom", each Text.Split([String],"@@")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Transform([Custom], each fnTableFromString(_, "$#$", null ) ))
in
    #"Added Custom1"

Let me know your thoughts and if you find this post helpful, don't hesitate to give it a kudos. 🙂

[v-chuncz-msft]

@danextian,

Thanks for sharing this.