Spit a column by specific number of character occurence

[chat_peters]

Hi,

I have a column called Category_Column I'd like to split into 3 different columns. 

Basically What I want is if there's one . that should be a separate column and so on.  I don't have to keep the dots. This is not a transpose problem as there are so many rows that say category III

.Category I

..Category II

…Category III

[v-junyant-msft · Accepted solution]

Hi @chat_peters ,

Please try this way.
You can use this DAX to create a new table:

New = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Category_Column]
    ),
    "Category 1",
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Table',
                LEFT('Table'[Category_Column], 1) = "." && LEFT('Table'[Category_Column], 2) <> ".."),
                RIGHT(LEFT('Table'[Category_Column], LEN('Table'[Category_Column])), LEN('Table'[Category_Column]) - 1),
                ", "
        )
    ),
    "Category 2",
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Table',
                LEFT('Table'[Category_Column], 2) = ".." && LEFT('Table'[Category_Column], 3) <> "..."),
                RIGHT(LEFT('Table'[Category_Column], LEN('Table'[Category_Column])), LEN('Table'[Category_Column]) - 2),
                ", "
        )
    ),
    "Category 3",
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Table',
                LEFT('Table'[Category_Column], 3) = "..."),
                RIGHT(LEFT('Table'[Category_Column], LEN('Table'[Category_Column])), LEN('Table'[Category_Column]) - 3),
                ", "
        )
    )
)

The results are as follows:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-junyant-msft · Accepted solution]

Hi @chat_peters ,

Please try this way.
You can use this DAX to create a new table:

New = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Category_Column]
    ),
    "Category 1",
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Table',
                LEFT('Table'[Category_Column], 1) = "." && LEFT('Table'[Category_Column], 2) <> ".."),
                RIGHT(LEFT('Table'[Category_Column], LEN('Table'[Category_Column])), LEN('Table'[Category_Column]) - 1),
                ", "
        )
    ),
    "Category 2",
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Table',
                LEFT('Table'[Category_Column], 2) = ".." && LEFT('Table'[Category_Column], 3) <> "..."),
                RIGHT(LEFT('Table'[Category_Column], LEN('Table'[Category_Column])), LEN('Table'[Category_Column]) - 2),
                ", "
        )
    ),
    "Category 3",
    CALCULATE(
        CONCATENATEX(
            FILTER(
                'Table',
                LEFT('Table'[Category_Column], 3) = "..."),
                RIGHT(LEFT('Table'[Category_Column], LEN('Table'[Category_Column])), LEN('Table'[Category_Column]) - 3),
                ", "
        )
    )
)

The results are as follows:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chat_peters]

@v-junyant-msft thank you for answering my question. I still need a little help and I am sorry for not posting this question properly.

I want to be able to do this on powerquery. I used text.contains and text.range but I can't get the desired results. 

Here's the link to the file. In this excel workbook I have given what I have vs the desired result. 

This is what I have. The colon is important because every occurence of the colon punctuates a new category and every category name/title begins with 3 periods. Every value under each category begins with 4 periods.

I want to split those categories into new columns and have the values under them populated. The picture below gives the desired result. 

[lbendlin · Accepted solution]

Careful - your request would result in variable number of columns. That is not something Power BI likes.  Instead,  use a well structured table with Category and Item.

There's another issue - this :  "…"  is actually a single character, and it is not consistent throughout your sample data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WetSwzK2oNLOk2EopVgfM1XMsKMhJhfOcEvOAEM71TcxLz4fzAnJKc2GcsNT01JLEpJxUJKOcE4uK8kuKEYalJuYheP7ZRQiDnUuTS3OTUouUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Category", Splitter.SplitTextByEachDelimiter({"…"}, QuoteStyle.Csv, false), {"Category.1", "Category.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Category.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Category", "Item"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter1",":","",Replacer.ReplaceText,{"Category"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Category"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value1",{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Item] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Category", "Item"})
in
    #"Removed Other Columns"

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...