Forum Discussion

dmouser's avatar
dmouser
Frequent Visitor
8 years ago

Culling for different data across a text string

Good morning all.  We've inherited a set of data sourced from SharePoint.  One of the key pieces of data we need to access and interact with is burried within a column populated from a workflow (which we don't have access to).  As far as we can tell, the content is structured as follows:

 

ID, Category, Status, Office, Due Date, Assigned Date, Update Date

 

We've tried a few things, including delimit based commas (,), and even separating into rows.  However, there are a few issues:

 

  1. Some entries have multiple rows (I believe broken by a carriage return), which caused us to get an extra 360 columns upon delimiting
  2. The last entry - regardless of row - does not have a carriage return.  Therefore the Update Date and ID are getting joined and act as one string

I have created a separate table with offices name that we can use as a reference and key off of.  What we'd like to achieve is returned Updated Date values for offices where a specific status is present.  I've tried multiple approaches, but am not familiar enough with DAX to know if SEARCH, FIND, CALCULATE, or FILTER are the best approach, or some combination thereof.

 

For example, can I set a search string to find a "Specific Task Status,[lookupvalue from Office table]" and reutrn the value after the sixth comma?  There are only 6 offices, so I don't have an issue creating 6 new columns and modifying the formulate to reference the office name.  Then if that office isn't within the string the value can be blank.

3 Replies

  • dmouser's avatar
    dmouser
    Frequent Visitor
    8 years ago

    Is it possible to string together Extract and Deliminate functions?  I tried splicing them together, but received an error:

     

    Split: = Table.TransformColumns(#"Removed Columns", {{"Log", each Text.AfterDelimiter(_, ",", 6), type text}})

    Deliminate: = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Log.1", Int64.Type}, {"Log.2", type text}, [etc. etc.]

     

    Equally, if I could limit the split or extraction to clauses (between commas) that only contain values I need, it would help remove the extra data I don't need.

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi dmouser ,

     

    "What we'd like to achieve is returned Updated Date values for offices where a specific status is present. "

     

    <--- Could you please clarify more details about "returned Updated Date values"? What's the logic of your expected result. If possible, can you give me some sample data for analysis and test?

     

    Regards,

    Jimmy Tao

    • dmouser's avatar
      dmouser
      Frequent Visitor
      8 years ago

      I need to be able to extract the third date value from strings of select data per the example below.  However, it's one long continious block.

      ",Correspondence,Assignment Process, Entire Deliverable,2017-12-26T15:33:42,,2017-12-26T15:33:42
4903,Correspondence,Assignment Process, Entire Deliverable,2017-12-26T15:33:42,,2017-12-26T15:33:42
4903,Correspondence,Office Completion,XYZ,2017-12-26T15:33:42,2018-01-12T16:00:00,2018-01-04T13:43:48
4903,Correspondence,Office Completion,ABC,2017-12-26T15:33:42,2018-01-12T16:00:00,2018-01-04T13:43:48"

      The structure is:

      ID, Category, Action, Area/Office, Assigned Date, Created Date, Modified Date

       

      Where a Action = Office Completion, I need to be able to pull out the office (i.e. XYZ, ABC), and the modified date, whcih in this instance aligns to the date/time that office submitted their data.

       

      I tried to delimit on commas (,), but as you can see after the Modified Date there is no comma, so when I delimit that date value co-joins with the ID from "row" below it.