Forum Discussion
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:
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!
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!
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.
7 Replies
- Greg_Deckler
Community Champion
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
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.