Forum Discussion

alfredorhz's avatar
alfredorhz
Helper I
2 years ago
Solved

Extract text from string

HI Community
I have a string, that I get from a PowerShell script, This script creates a report from SharePoint page views, so, in the context of the information the audit data has a very large string.
I need extract some parts of the string but I need a search all registers that start with "http" and finish ".aspx" or ".pdf" words

this is the example register that I need to extract

The delimiter by default ( point, comma, quotes, etc) doesn't work, this is because some operations, types, or audit data change in size even in localization


Thanks for the help

Need Help
Show and Tell
Tips and Tricks
  • alfredorhz's avatar
    alfredorhz
    2 years ago

    Thanks guys

    I resolve this with a dax,

    Extract =
    var fin = LEFT(Table[AuditLog], SEARCH("http",Table[AuditLog])-1)
    var Lentf = LEN(fin)
    var Midd = MID(Table[AuditLog], Lentf, 250)
    #This is a delimiter before .aspx or .pdf acces file
    var     http = SEARCH(",", Midd)
    return
    LEFT(Midd, http)

7 Replies

    • alfredorhz's avatar
      alfredorhz
      Helper I
      2 years ago

      Hi Ahmedx
      Thanks
      It´s a little dificult share this files, you know, company sharing restrictions

  • christinepayton's avatar
    christinepayton
    Most Valuable Professional
    2 years ago

    This looks like JSON maybe? There is a "parse JSON" button in the toolbar in Power Query, if you use that on the field it will let you expand out specific parts as columns without needing to split on characters. 

    • alfredorhz's avatar
      alfredorhz
      Helper I
      2 years ago

      Hi Chirstine
      thanks

      Its a colum of an power shell extraction report, its a csv file

      I tried split columns before, but the process splits a lot of columns, I was looking for a specific solution

      • christinepayton's avatar
        christinepayton
        Most Valuable Professional
        2 years ago

        The file is CSV but the text in that column looks like JSON - did you try the parse JSON button with that column selected? I have done this with CSV exports from the audit logs personally, so it does work. Parse and then expand out the fields you want into new columns.

         

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    the whole file is not needed, you create a few examples and paste the court

  • alfredorhz's avatar
    alfredorhz
    Helper I
    2 years ago

    Thanks guys

    I resolve this with a dax,

    Extract =
    var fin = LEFT(Table[AuditLog], SEARCH("http",Table[AuditLog])-1)
    var Lentf = LEN(fin)
    var Midd = MID(Table[AuditLog], Lentf, 250)
    #This is a delimiter before .aspx or .pdf acces file
    var     http = SEARCH(",", Midd)
    return
    LEFT(Midd, http)