Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Possible to sort cells?

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...
Need Help
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

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 ":".