Split a mixed data type column into hyperlink and text

[tomekm]

Hello,

I have an excel column (lets call it: “more info”) that contains rows with mixed data: text and hyperlinks in each cell. When imported into Power BI, I would like to split the “more info” column into 2 new columns: one to isolate the text and the other one to isolate the hyperlink.

But when I import this file into power bi, both the text portion and the hyperlinks are treated as text, and changing it to “web URL” in the data type does not do anything (PBI does not differentiate whats text and whats hyperlink for each cell; the hyperlink portion gets converted to text).

Any ideas on how to split the column into 2: a) the text, b) the hyperlink? Or how to convert the hyperlink into an actual URL in Power bi (and not excel)?

Thank you.

[v-rongtiep-msft · Accepted solution]

 Hi @tomekm 

In the situation, you can use Power BI Split Columns option to split that column into multiple columns.Please refer to the following document to see if it helps you.

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @tomekm 

Can you share a sample of your data in a table format?

BTW, you can split them by using SEARCH or FIND and then use Len, Right or left.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[tomekm]

Here is the sample data:

Folder Path	Subject	Importance	HasAttachments	"More info" column:
\Calendar\	ABC meeting	Normal	False	Meeting minutes <<<<<<<< (this is the hyperlink)   >>>>>> this is the text: Join the meeting to discuss important topics…..

[Anonymous]

Agreed, a sample dataset would be nice.  If you can identify a reliable delimeter then it's pretty straightforward and could be done in Power Query.  Even without a consistent delimiter there could still be some options in PowerQuery or DAX.

[tomekm]

Here is the sample data:

Folder Path	Subject	Importance	HasAttachments	"More info" column:
\Calendar\	ABC meeting	Normal	False	Meeting minutes <<<<<<<< (this is the hyperlink)   >>>>>> this is the text: Join the meeting to discuss important topics…..