Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
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.
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
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.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
73 | |
57 | |
38 | |
35 |
User | Count |
---|---|
83 | |
68 | |
59 | |
46 | |
45 |