Newbie to BI trying to pull apart a string of a turnaround time cycle

[adam_w]

Hi all,

New job, new lab and never used Power BI before!

Our project software when I run a job turnaround breakdown report creates the following string as each job moves through different stages of the process. The numbers listed are the days that it sits in each part of the cycle. Example here: 

Received: 1  In Work: 0  Awaiting Repair: 21  Awaiting Acceptance: 9  Awaiting Repair: 0  Awaiting Parts: 0  Complete: 1

What is the best way to pull apart this string into columns? I would like a column for each part of the work cycle, with empty columns where needed.

Potential problems I notice with the string is:

1. If a job doesn't go into a particular process, that wording is not included in the string, so the strings vary in length. Examples:

Received: 1  In Work: 0  Awaiting Calibration: 0  Complete: 3

Received: 2  In Work: 11  Awaiting QA: 3

Received: 3  In Work: 9  Awaiting QA: 0

Received: 1  In Work: 0  Awaiting Calibration: 0  In Work: 0  Awaiting QA: 0  Complete: 3

Received: 9  In Work: 6  Awaiting Repair: 2  In Work: 0  In Work: 2  Awaiting Procedure: 15  In Work: 10  Awaiting QA: 0  Complete: 7

2. Sometimes parts are repeated and can include multiple entries  eg   "In work" can appear more than once - see example 5 above where I would want 12 days "In Work" (10 + 2).

3. Sometimes parts are missing and sometimes they get a 0 value - still working out why that is!

Thanks for your help.

[PiEye · Accepted solution]

Okay, so this is a tough one!

I tried several things including trying to fiddle with the text and make it "more like" JSON, as PowerBI has a JSON parser, but was thrown by the duplicates. If you can get it in JSON, please do!

Eventually, I was able to get the text into columns, via several steps:

1 - Find a way to mark the boundaries between job types

We want it to look like this, so that we can use the markers to split the string in a later step.

One way to achieve this is to create a table of Replacement Words, and then apply this as a substitution to each cell in the data.

Replacement words table: Delimeter can be pipe, comma, whatever is appropriate

Code for custom column in the source data:= Table.AddColumn(#"Removed Columns", "Pipe Delimeted", each List.Accumulate( Table.ToRecords( #"Replacement Words" ), [Column1], ( valueToReplace, replaceOldNewRecord ) => Text.Replace( valueToReplace, replaceOldNewRecord[Old], replaceOldNewRecord[New] ) ))This will result in the a field called "pipe delimited" as below:

2 - Split the pipe delimited into rows

Use the "Split Column by delimiter" wizard under the text portion of the ribbon:

Set the delimiter to custom, "|" and under advanced options, select rows

 Your data will look like this:

 

3 - Split the result into columns using the semi colon as delimiter

Similar to before, but with the ":" as delimiter and into columns:

 

4 - Pivot the data into columns

 Use the pivot columns wizard to create the columns you need. Pivot on the "Task" column (job description) and remember to tick "sum" for aggregate values

This results in the below:

 

Hope this helps, let me know if the steps all work for you. 

Luckily with the exception of the first step, this can all be achieved using the wizards.

Pi