Forum Discussion
Convert data from one form to another
- first click on your column with the 3 statuses,
- then in the Transform ribbon click on the "Split Column" button
- then chose the "by Delimiter" option and choose "/" as your delimiter (Power Query should detect this)
- then click on ok, this should split your column into 3 numeric values
- rename the 3 new columns to "Comp", "Pend" and "Overdue"
- then click on the "Add Column" ribbon
- then click on "Custom Column"
- and paste in the following code
if [Comp] = 0 and [Pend]= 0 and [Overdue] = 0 then "No Actions Defined" else if [Comp] > 0 and [Pend] > 0 then "Partially Complete" else if [Comp] > 0 then "Complete" else if [Pend] > 0 then "Pending" else if [Overdue] > 0 then "Overdue" else "Other"
- first click on your column with the 3 statuses,
- then in the Transform ribbon click on the "Split Column" button
- then chose the "by Delimiter" option and choose "/" as your delimiter (Power Query should detect this)
- then click on ok, this should split your column into 3 numeric values
- rename the 3 new columns to "Comp", "Pend" and "Overdue"
- then click on the "Add Column" ribbon
- then click on "Custom Column"
- and paste in the following code
if [Comp] = 0 and [Pend]= 0 and [Overdue] = 0 then "No Actions Defined" else if [Comp] > 0 and [Pend] > 0 then "Partially Complete" else if [Comp] > 0 then "Complete" else if [Pend] > 0 then "Pending" else if [Overdue] > 0 then "Overdue" else "Other"
Facing below error on doing the above procedure
Expression.Error: The field 'Pend' of the record wasn't found.
Details:
Report Number=Obs-2017-000182
Observation Date=1/2/2017 11:23:00 PM
Observation Time=23:23 (UTC+05:30) IST
Observation Method=Safety Walkaround
Site - Location No=43685
Division - Divisional Region Adhesive Technologies=MEA
Region=Asia
Country=India
Location Type=active
Site=Chennai
Division=Adhesive Technologies
Sub-division=AT
Employee function=
Team Member=Prabhu Thiagarajan
Comments=Near Packing material storage area in Extruder cooling line water leakage
Comp=1
pend=0
Overdue=0
Created By=Prabhu Thiagarajan
Created Date=1/2/2017 5:57:17 PM
Observation Location=Extruder shop floor
Safety Walkaround - Walkaround Focus=Safety
STOP Audit *(STOP is trademark of DuPont) - Audit Focus=
Safety Observation - Description :(Include name of the original reporter if applicable)=
SUSA Conversation - Location (Area):=
SUSA Conversation - Observed Type=
SUSA Conversation - Probability:=
SUSA Conversation - Consequence:=
Result Number=
Comment=
Category=
Sub Category=
Class of Observation=
Action Item Number:=Obs-2017-000182_1
Action Item Description=Near Packing material storage area in Extruder cooling line water leakage - Leakage to be arrested as soon as possible
Assigned To=Suresh Mungara
Target Date:=1/31/2017
Completion Date:=1/18/2017
Inactive-Does the observation have:=
Index=1
Anonymous wrote:
Facing below error on doing the above procedure
Expression.Error: The field 'Pend' of the record wasn't found.
That most likely means you called the second column something different