Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |