Forum Discussion

alfredorhz's avatar
alfredorhz
Icon for Helper I rankHelper I
3 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 stri...
Need Help
Show and Tell
Tips and Tricks
  • alfredorhz's avatar
    alfredorhz
    3 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)
christinepayton's avatar
christinepayton
Icon for Most Valuable Professional rankMost Valuable Professional
3 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
Icon for Helper I rankHelper I
3 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
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 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.