Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

Jump to Best Answer
  • 1.  String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Bronze Contributor
    Posted Aug 10, 2020 11:46 PM
    Hi All,

    Part of the data set I am working with has a text column that uses ";" and "~" to denote rows and columns respectively, for example:

    "ABC~1.2~30~A;DEF~1.1~20~A" is a 2 row by 4 column "table"
    "1;2;3" is a 3 row by 1 column "table"

    The text column I am looking at specifically is x row by 5 column "tables" stored as strings, with x ranging from 0 to 20. I am trying to find a way to extract a specific "column" from these strings to sum, for example:

    if the text string is "ABC~1.2~30~A~1;DEF12~1.1~20~A~1;GHGI~1~5~B/C~1", I need to extract the 2nd "column" (the portions bolded and underlined) for each "row" and sum them (in this case giving 3.3 as the value)

    Power Query split by delimiter function doesn't exactly achieve what I need, but if someone knows how to tweak this to achieve the above, do share as well.

    I was able to achieve the extraction in Power Apps by using the following formula:

    Sum(AddColumns(Split("ABC~1.2~30~A~1;DEF12~1.1~20~A~1;GHGI~1~5~B/C~1",";").Result,"WL",Last(FirstN(Split(Result,"~").Result,2)).Result),WL)

    However, it seems Power BI doesn't have a Split() function available?

    If anyone can point me in the right direction, it would be a big help!

    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Posted Aug 11, 2020 02:26 AM
    Hi Zhi,

    I have to admit that I have not done a lot with text splitting in PBI as I would handle this at the source. I do however understand that sometimes this is just not possible.

    I have been doing some hunting and have some possible pointers;
    https://blog.crossjoin.co.uk/2014/08/11/comparers-combiners-replacers-and-splitters-in-power-query/
    https://blog.crossjoin.co.uk/2019/02/12/splitting-text-by-character-transition-power-bi-power-query-excel/
    https://community.powerbi.com/t5/Desktop/Split-text-string-column-values-into-multiple-rows-at-a-position/td-p/480089#
    https://community.powerbi.com/t5/Desktop/DAX-How-to-Split-left-a-text-column-on-Character-space/td-p/44584#

    Hope these help, would love to hear what you use and how it worked out for you.

    Thanks,
    David

    ------------------------------
    David O'Neill
    System Manager
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?
    Best Answer

    Posted Aug 11, 2020 02:48 AM
    Edited by Dan Robbins Aug 11, 2020 05:56 AM
    The Power Query Split function along with a Transpose and Column addition will give you what you want I believe.

    "3.3"

    Started with your string of text
    Broke it into 3 rows
    Split the 3 row into 5 columns
    Deleted all but the 2 column
    Transposed the columns to a row
    Added the 3 column in a 4th column

    Below is the M Language code I used.  You should be able to paste it into a blank query to look at it further and tweak it.
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyrjPUM6ozNqhzrDO0dnF1MzQCChjWGUEE3D3cPesM60zrnPSBCpViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type number}, {"Column1.3", Int64.Type}, {"Column1.4", type text}, {"Column1.5", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.1", "Column1.3", "Column1.4", "Column1.5"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Inserted Sum" = Table.AddColumn(#"Transposed Table", "Addition", each List.Sum({[Column1], [Column2], [Column3]}), type number)
    in
    #"Inserted Sum"



    ------------------------------
    Dan Robbins
    BI Developer
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Bronze Contributor
    Posted Aug 11, 2020 05:52 AM
    Nice!

    I trimmed down your power query and arrived at the following, which yields the result I need:

    let func1 = Splitter.SplitTextByDelimiter(";"),
    output1 = func1([<name of table column here>]),
    output2 = Table.FromList(output1,Splitter.SplitTextByDelimiter("~"),{"a","b","c","d","e"}),
    output3 = Table.TransformColumnTypes(output2,{"b", Int64.Type}),
    output4 = List.Sum(Table.Column(output3,"b"))
    in output4

    On a related note, I was trying to "slice" into the table python style by using integers, but it doesn't seem to work. Is it possible to call a column without assigning it a name first?

    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Bronze Contributor
    Posted Aug 11, 2020 05:56 AM
    Thanks!

    I did further digging and most leads ended up back in Microsoft's own documentation:

    https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

    While some parts of it is difficult to digest, some google-fu let me find the approximate functions to rebuild the Split function in Power BI.

    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Posted Aug 11, 2020 11:31 AM
    Edited by Dan Robbins Aug 11, 2020 11:32 AM
    Yes, it can be done with a function.  However, I think a function would be overkill.

    I ask myself, what do I gain by avoiding the creation of a column?  Do I end up with an elegant solution at the expense of maintainability?

    For many of us it is likely that someone else might have to touch our Power Queries.   Have we created routines with a common flow that are easily understood?

    In most cases the simple solution is the best solution.

    ------------------------------
    Dan Robbins
    BI Developer
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Silver Contributor
    Posted Aug 12, 2020 10:37 AM
    Maybe I'm not understanding the requirement, but based on what I read, there is a simpler solution.  It is not perfectly elegant, in that the selection of the second column is hard coded, but I did not see that you were looking for a dynamic column selection:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(
    Binary.FromText("i45WcnRyrjPUM6ozNqhzrDO0dnF1MzQCChjWGUEE3D3cPesM60zrnPSBCpViYwE=", BinaryEncoding.Base64),
    Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    SplitColumn = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    TransposeTable = Table.Transpose(SplitColumn),
    ExtracteTextAftDelimiter = Table.TransformColumns(TransposeTable, {{"Column1", each Text.AfterDelimiter(_, "~"), type text}}),
    ExtracteTextB4Delimiter = Table.TransformColumns(ExtracteTextAftDelimiter, {{"Column1", each Text.BeforeDelimiter(_, "~"), type text}}),
    ChangeType = Table.TransformColumnTypes(ExtracteTextB4Delimiter,{{"Column1", type number}}),
    SumCol = List.Sum(ChangeType[Column1])
    in
    SumCol


    ------------------------------
    Jeff Nixon
    Managing Director
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Bronze Contributor
    Posted Aug 12, 2020 08:24 PM
    Thanks!

    I arrived at a similar solution as you have pointed out:

    let func1 = Splitter.SplitTextByDelimiter(";"),
    output1 = func1([<name of table column here>]),
    output2 = Table.FromList(output1,Splitter.SplitTextByDelimiter("~"),{"a","b","c","d","e"}),
    output3 = Table.TransformColumnTypes(output2,{"b", Int64.Type}),
    output4 = List.Sum(Table.Column(output3,"b"))
    in output4

    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: String Manipulation Without Using Power Query - Is there a Split() function substitute in Power BI?

    Top Contributor
    Posted Aug 13, 2020 08:01 AM
    The standard approach in DAX is to replace your delimiters with pipe ( " | " ) and then use the PATH function family.

    ------------------------------
    Lutz
    ------------------------------

    Academy - Online Interactive Learning from Experts