Forum Discussion

tomekm's avatar
tomekm
Helper III
4 years ago
Solved

Split a mixed data type column into hyperlink and text

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

     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.

     

5 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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!!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tomekm
        Helper III
        4 years ago

        Here is the sample data:

         

        Folder Path

        		SubjectImportanceHasAttachments"More info" column:
        \Calendar\ABC meetingNormalFalseMeeting minutes <<<<<<<< (this is the hyperlink)  
        >>>>>> this is the text: Join the meeting to discuss important topics…..
    • tomekm's avatar
      tomekm
      Helper III
      4 years ago

      Here is the sample data:

       

      Folder Path

      		SubjectImportanceHasAttachments"More info" column:
      \Calendar\ABC meetingNormalFalseMeeting minutes <<<<<<<< (this is the hyperlink)  
      >>>>>> this is the text: Join the meeting to discuss important topics…..
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     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.