Forum Discussion
Possible to sort cells?
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.
Community ChampionAre 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.
- 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)RETURNMID([Column1],__pos + __len, __semipos - (__pos + __len)) - 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 ":".
