Possible to sort cells?

[Plagiatus]

Hello everyone
i want powerBi to automatically sort something within a cell. Is this even possible? Because, I found nothing regarding cells in PowerBI so far on the internet.

Anyhow my problem is:
In one cell are more information’s than just one and I want them to be splitted. Sounds easy so far. But the issue here is

Its always in a different order. For Example in the first row its: Colour: Red; Name: abc; Number: 123; … and so on. There are always 10 categories within one cell.
In the next row they are mixed up so I cant just use the Split function for the columns.

So how do I tell PowerBI – Within the Column [Column1] if there is “Colour” create a new column and write everything down until the semicolon.
Or
If the cell contains “Colour” copy until semicolon to [ColumnA]

Im kinda helpless right now and maybe there is a simple way to solve it I just can’t think of anything right now.

I´d be grateful if you could help me.
Thanks!

[Greg_Deckler · Accepted solution]

Well, you could get there via DAX but it would be fairly nasty. You would need to create a column for each of your 10 things. You would use a formula like the following for each:

Column = 
    VAR __find = "Colour: "
    VAR __pos = SEARCH(__find,[Column1])
    VAR __semipos = SEARCH(";",[Column1],__pos)
    VAR __len = LEN(__find)
RETURN
    MID([Column1],__pos + __len, __semipos - (__pos + __len))

See attached.

[Plagiatus]

I'm really not that good with programming.. Uh there is an error in the first line find = "colour:" it says expected token : eof

[Greg_Deckler]

Are you getting that error in the PBIX I attached? Could be something wonky with the copy and paste of the double quotes if you are copying and pasting out of the forum.

[Plagiatus]

Ah it's not compatible with my version. I'm having the March version here and I can't install the November one on my laptop from work

[Greg_Deckler]

Wait a minute, another thought, you are pasting this into the DAX formula bar in Power BI Desktop and NOT trying to paste it into a new column in the Power Query Editor correct? Because if you are doing the latter, it will not work at all, what I presented was a DAX solution, not a Power Query (M) solution.

If you just have to have a Power Query (M) solution, then @ImkeF  is a good bet.

[ImkeF]

Hm, not sure if my understanding is correct. But please paste the following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PyS8tslIISk2xVvBLzE21UkhMSgYyS3OTUoHihkbG1kqxOtFKEDn3otR0awWYJvf8nJTUPGsFx7z8kozUIrgmAwNzoKZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    fnCleanAndTrimList = (TheList as list) => List.Transform(TheList, each Text.Trim(Text.Clean(_))),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.SplitAny([Column1], ":;")),
    Values = Table.AddColumn(#"Added Custom", "Values", each fnCleanAndTrimList(List.Alternate([Custom],1,1))),
    ColumnNames = Table.AddColumn(Values, "ColumnNames", each List.FirstN(fnCleanAndTrimList(List.Alternate([Custom],1,1,1)), List.Count([Values]))),
    CreateTable = Table.AddColumn(ColumnNames, "ConsolidatedTable", each Table.FromRows({[Values]}, [ColumnNames])),
    #"Expanded ConsolidatedTable1" = Table.ExpandTableColumn(CreateTable, "ConsolidatedTable", List.Union(CreateTable[ColumnNames]) )
in
    #"Expanded ConsolidatedTable1"

It splits up the text in each cell and creates new columns for every item that stands before a ":". 

[Greg_Deckler]

Hmm, you might try pasting it into notepad and then into the DAX formula bar. Or, simply try replacing the double quote characters in the DAX formula bar?

I will paste it here outside of a code block, perhaps that will work better if you cut and paste this instead

Column =

    VAR __find = "Colour: "

    VAR __pos = SEARCH(__find,[Column1])

    VAR __semipos = SEARCH(";",[Column1],__pos)

    VAR __len = LEN(__find)

RETURN

    MID([Column1],__pos + __len, __semipos - (__pos + __len))