Power BI Query Editor: Split Text with Variable Delimiters (Q&A)

Followup to Mikhail's question in today's Talk Power BI.

===Most Popular Playlists===

⚡Power On!⚡
-Avi Singh

[leebecker]

Hi, this is really useful. Is there a way to do this but into columns instead of into rows? Thanks!

[zQxLT]

Thanks Avi.. for a simple solution. Earlier, I was using "right click and splitting the field" but I guess.. this is more accurate way of doing.

[pawewrona]

@Noble One, it depends if these multiple columns contain the same information, or not. If they do, you could combine them first and use the same trick presented here by Avi. If they contain different kind of information I think it makes sense only to do the split for each column in separate step.

[shoaibahmedkhan]

Quick come back. Appreciated. It's the passion that drives us.

[leesantos]

HI Avi, can this be done via DAX query?

[fvarelacid]

Hello Avi.
And if I just want to count the text that is the same? For example if in this case I just want to count how many "PowerBI" skill appears in the column with out duplicating lines? Because when I get the results from a satisfaction form I just want to see how many people chose 1 option and don't have duplicated data on the other columns.
Was I clear enough? And Thanks for this Explanation!

[HectorGarciaCPA]

very nice!


btw, what webcam are you using??... it has a cool angle.

[ursusmaritimus]

Thank you, Avi, very quick reply and easy solution. I’ll keep asking questions.... unfortunately or fortunately some questions will be from my Qlik experience, cause I am trying to recreate some apps from Qlik in Power BI and don’t know how it works in PowerBI...

[ihatekutaz]

Hi. I need to split a line in EACH cell in custom column in power BI. For instance, I would want the following: Line 1: Data from Column 1 + Data from Column 2 + "!!"  THEN Line 2: Data from Column 3 + "!!!!" . My question is, how do I include the line break. Thanks

[hairstyleshort]

Hello Avi, I am having trouble with something similar, I need to extract a portion of a text without any delimitation, problem is, if the text ends with XLZA, it needs to split the last 4 letters, but if it is anything else, it needs to split the last 3 letters. How can this be done? In a normal spreadsheet I used IF, COUNTIF, LEN, but I cannot replicate the same in power query. Thanks.

[shanthisugunabharadwaj]

Hi Avi. I have the same request. Are you able to delimit with multiple columns. I'm able to do this using the split column feature however, my column contains alphanumeric values - something like "fjfrtfdgkhkg 233g; 0082985W; s3q4cwh" and column 2 comes up as error instead of showing 0082985W value.

[thephenomDD]

Great Video! Avi, after creating a new table that contains the split custom column, my other visuals don't interact with the visual that is mapped to my split table. Power Bi did create a many-to-one relationship. How can I get my other visuals to interact (filter) when I select a value from the table (visual) that contains my split column?

[LizzO]

Thank you! that was a great solution! :)

[swapnilamundra]

Can you make a video on how to extract tables from the html column coming from SQLserver?

[gayatripordwal]

Hello Avi, can we run a procedure in power BI

[vippankapoor]

Hi Avi, Need help with nesting something equivalent of Mid and Find under Power BI. I need to pull 8 characters series starting with 9 from text e.g. extracting from cell with string "P11 Alloc Parked Invoice". I don't want to use split column function as that  does not provide clean results in one column. TIA

[DanielWeikert]

Wondering why SSAS doesn't show up. Works for me. Thanks for the video Avi. I used another solution. That's what's nice about Power BI. There's always more ways to skin a cat