let
Source = Csv.Document(File.Contents("C:\Users\OneDrive - W\G\IData\export.csv"),[Delimiter=",", Columns=158, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ITEMID", Int64.Type}, {"TYPE", type text}, {"CHALLENGE TITLE", type text}, {"CHALLENGE THEME", type text}, {"CHALLENGE STATUS", type text}, {"TITLE", type text}, {"DESCRIPTION", type text}, {"TAGGED USERS", type text}, {"What brand does your idea relate to?", type text}, {"How might we resolve this problem or take advantage of the opportunity? ", type text}, {"Which of our three key priorities does this relate to?", type text}, {"How might this change benefit our team and/or customers?", type text}, {"Which area of your business do you work in?", type text}, {"How long do you spend on resolving the issue that this idea will solve?", type text}, {"How might we resolve this problem or take advantage of the opportunity? _1", type text}, {"How might this change benefit our team and/or customers?_2", type text}, {"How might we resolve this problem or take advantage of the opportunity? _3", type text}, {"How might this change benefit our team and/or customers?_4", type text}, {"How might this change benefit our team and/or customers?_5", type text}, {"If we were to implement this idea what would the benefit be", type text}, {"If Yes please note the complaint reference number here", type text}, {"How might this change benefit our team and/or customers?_6", type text}, {"How might we resolve this problem or take advantage of the opportunity?", type text}, {"How might this change benefit our team and/or customers?_7", type text}, {"How might we resolve this problem or take advantage of the opportunity?_8", type text}, {"(MODERATOR ONLY) - Idea Owner", type text}, {"If possible please provide date & time of any customer call your suggestion relates to (DO NOT INCLUDE PERSONAL CUSTOMER INFORMATION) ", type text}, {"(MODERATOR ONLY) - Stages", type text}, {"How many customers would benefit from this suggestion per week? This can be volume of calls or customer touch points.", type text}, {"How might we resolve this problem or take advantage of the opportunity? _9", type text}, {"Would this change the time it takes to complete this task? If yes approx by how many mins?", type text}, {"How might we resolve this problem or take advantage of the opportunity?_10", type text}, {"How might we resolve this idea or take advantage of the opportunity? ", type text}, {"Which brand does your idea relate to?", type text}, {"Please select your role:", type text}, {"Will this change the time it takes to complete this task?", type text}, {"How might we resolve this problem or take advantage of the opportunity?_11", type text}, {"How many customers would benefit from this suggestion per week? Volume of customer touch points, impact to task completion time", type text}, {"Please confirm there is no customer information or attachments in this Iidea", type text}, {"What would be the benefits of the change?", type text}, {"Does this idea relate to a complaint? ", type text}, {"(MODERATOR ONLY) - Started Date", type text}, {"Has the change request checklist been completed and sent to your Risk Manager for review? ", type text}, {"Did this idea arise from a complaint?", type text}, {" Has a Risk assessment been completed ?", type text}, {"What business area are you from?", type text}, {"How might we resolve this problem or take advantage of the opportunity? _12", type text}, {"Estimated Work Hours Saved", type text}, {"Which brand/s does your idea relate to?", type text}, {"(MODERATOR ONLY) - Who does this impact? ", type text}, {"Select your State", type text}, {"How might we resolve this problem or take advantage of the opportunity? _13", type text}, {"What role or job family will benefit most from this idea?", type text}, {" Has a Risk assessment been completed ?_14", type text}, {"Which area or village does your idea associate to?", type text}, {"Which 1 lens does your idea relate to?", type text}, {"(MODERATOR ONLY) - Effort ", type text}, {"(MODERATOR ONLY) - Issue About", type text}, {"How might we resolve this problem or take advantage of the opportunity? _15", type text}, {"How might this change benefit our team and/or customers?_16", type text}, {"What are your solutions for this idea?", type text}, {"Select your Site ", type text}, {"(People Leader Only) People Leader Approval", type text}, {"What are the client benefits (if applicable)?", type text}, {"Please confirm that there is no customer information or attachments in this Ignite Idea", type text}, {"(MODERATOR ONLY) - Type", type text}, {"How might this change benefit our team and/or customers?_17", type text}, {"(MODERATOR ONLY) - Issue Is", type text}, {"Does this idea relate to a complaint? _18", type text}, {"Is this Fix, Simplify or Perform", type text}, {"What impact/s is this having on our business", type text}, {"What Team are you from?", type text}, {"What area of GO do you work in?", type text}, {"Are you a people leader? ", type text}, {"How might we resolve this problem or take advantage of the opportunity? _19", type text}, {"How might this change benefit our team and/or customers?_20", type text}, {"How might we resolve this problem or take advantage of the opportunity? _21", type text}, {"(MODERATOR ONLY) - Theme", type text}, {"(MODERATOR ONLY) - Accountable ", type text}, {"How could your idea benefit our team and/or customers?", type text}, {"If Yes please note the complaint reference number here_22", type text}, {"Please add your technical request number eg JIRA or Number if known", type text}, {"Please add your technical request number eg JIRA or SharePoint Number if known_23", type text}, {"(MODERATOR ONLY) - Time to Solve", type text}, {"Is this idea as a result of customer feedback or complaint?", type text}, {"Which brands does this idea impact?", type text}, {"If you work for Genpact or Concentrix please indicate which one", type text}, {"(MODERATOR ONLY) - Leadership Team Owner", type text}, {"Select your segment", type text}, {"Has a benefit calculation been completed?", type text}, {"If you are making changes to customer data, have you completed and attached a DPIA and emailed a copy to your risk manager prior to implementation?", type text}, {"How might this change benefit our team and/or customers?_24", type text}, {"System this relates to eg Murex, Calypso, Quick Service, TLM (Transaction Lifecycle Management) Service Online, Loan IQ, Fenergo", type text}, {"How could your idea benefit our team and/or customer? ", type text}, {"How might this change benefit our team and/or customers? (include any risks this may mitigate)", type text}, {"(MODERATOR ONLY) - Date Projected", type date}, {"Estimated delivery date MM/YY", type date}, {"What Solution and Support Do You Think Would Address This?", type text}, {"Are there any risks we need to manage or mitigate?", type text}, {"How might we resolve this problem or take advantage of the opportunity? _25", type text}, {"(MODERATOR ONLY) - Next Update", type text}, {"How might we resolve this problem or take advantage of the opportunity? _26", type text}, {"How might this change benefit our team and/or customers?_27", type text}, {"(MODERATOR ONLY) - Customer Journey Phase", type text}, {"How might we resolve this problem or take advantage of the opportunity? _28", type text}, {"Do you require Management Support to progress?", type text}, {"(MODERATOR ONLY) - Solution Detail", type text}, {"Is this Fix, Simplify or Perform_29", type text}, {"Estimated delivery date MM/YY_30", type text}, {"(MODERATOR ONLY) - Priority", type text}, {"How might this change benefit our team and/or customers?_31", type text}, {"(Change Champion Only) Have Benefit Calculations Been Completed?", type text}, {"How might we resolve this problem or take advantage of the opportunity? _32", type text}, {"How might this change benefit our team and/or customers?_33", type text}, {"Please confirm there is no customer information or attachments in this Ignite idea_34", type text}, {"(MODERATOR ONLY) - Product", type text}, {"Please select the business unit this idea belongs to", type text}, {"(People Leader Only) People Leader Feedback - Please enter your reasoning for approval / decline", type text}, {"(Change Champion Only) Is This A Continuous Improvement Or Business Initiative?", type text}, {"Which brand does your idea relate to?_35", type text}, {"If this idea was to proceed, are there any associated risks to be managed / mitigated? ", type text}, {"Which 10CSE lens does your idea relate to?_36", type text}, {"If you work for Genpact or Concentrix please indicate which one_37", type text}, {"Is there a natural owner to support execution of the suggestion, and if so who?", type text}, {"How many transactions or clients are impacted weekly by the issue that this idea will solve?", type text}, {"How do you think i will support us in building a strong Continuous Improvement culture and help make it EASY to do business?", type text}, {"System this relates to eg Mu, Cal, QService, TLM Service Online, F38", type text}, {"(Change Champion Only) Please specify the hard and soft benefits", type text}, {"Has a benefit calculator been completed? ", type text}, {"Is this a quick win that has already been implemented?", type text}, {"Has RA been completed? ", type text}, {"If yes, please note the date of Implementation below", type text}, {"(Change Champion Only) Will Process Maps/Procedures/Collateral Require Updating From The Implementation Of This Idea?", type text}, {"If a JIRA case has been created, please reference the JIRA number below", type text}, {"(MODERATOR ONLY) - Change Owner", type text}, {"Have you completed a Delivery Checklist prior to implementing this change?", type text}, {"Which area of your business do you work in?_39", type text}, {"(Phone Teams Only) If possible provide date/time of call:", type text}, {"Has a benefit calculation been completed?_40", type text}, {"(Change Champion Only) Has an Impact Assessment Been Completed?", type text}, {"How might we roll out your idea? ", type text}, {"Who is your people leader?", type text}, {"Time Savings Benefit Number (show number only in hours per week) * Please ensure you have added the Benefits Calculator as a link in the comments section ASAP*", Int64.Type}, {"Has the change request checklist been completed and sent to your Risk Manager for review? _41", type text}, {"Current RAG Rating ", type text}, {"(MODERATOR ONLY) - Trigger", type text}, {"How might this change benefit our team and/or customers? (include any risks this may mitigate and or links to your benefit calculation)", type text}, {"SCORE", Int64.Type}, {"LAST REACHED MILESTONE", type text}, {"FIRST PHASE", type text}, {"PHASE REACHED", type text}, {"STATUS LABELS", type text}, {"AUTHOR", type text}, {"AUTHOR EMAIL", type text}, {"CO-AUTHORS EMAILS", type text}, {"DATE (YYYY-MM-DD)", type date}, {"TIME (GMT)", type time}, {"COMMENT COUNT", Int64.Type}},MissingField.Ignore),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Status_Label2", each [STATUS LABELS],MissingField.Ignore),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "STTSTEST", each [STATUS LABELS]),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Added Custom1","Status After Delimiter", each Text.AfterDelimiter([STTSTEST], ",", {0, RelativePosition.FromEnd}), type text),
#"Added Custom2" = Table.AddColumn(#"Inserted Text After Delimiter", "STGES", each (if ([Status After Delimiter]="Acknowledged" or [Status After Delimiter]="Under Investigation" or [Status After Delimiter]="Seeking Clarification" or [Status After Delimiter]="Implementing"or [Status After Delimiter]="On hold" or [Status After Delimiter]="Review Phase" or [Status After Delimiter]="To be discussed at Governance" or [Status After Delimiter]="Fast Track" or [Status After Delimiter]="Master Post" or [Status After Delimiter]="On Hold" ) then "Open" else if ([Status After Delimiter]="Existing Solution" or [Status After Delimiter]="Implemented" or [Status After Delimiter]="Not for Now" or [Status After Delimiter]="Not Viable" or [Status After Delimiter]="Duplicate Post" or [Status After Delimiter]="No Response Received" or [Status After Delimiter]="Closed due to Inactivity" or [Status After Delimiter]="Handed over to a Project" or [Status After Delimiter]="Not for now" or [Status After Delimiter]= "Closed due to inactivity") then "Closed" else "Inflight")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"STGES"}),
#"Added Custom3" = Table.AddColumn(#"Removed Columns", "STTUS", each (if ([Status After Delimiter]="Acknowledged" or [Status After Delimiter]="Under Investigation" or [Status After Delimiter]="Seeking Clarification" or [Status After Delimiter]="Implementing"or [Status After Delimiter]="On hold" or [Status After Delimiter]="Review Phase" or [Status After Delimiter]="To be discussed at Governance" or [Status After Delimiter]="Fast Track" or [Status After Delimiter]="Master Post" or [Status After Delimiter]="On Hold" ) then "Open" else if ([Status After Delimiter]="Existing Solution" or [Status After Delimiter]="Implemented" or [Status After Delimiter]="Not for Now" or [Status After Delimiter]="Not Viable" or [Status After Delimiter]="Duplicate Post" or [Status After Delimiter]="No Response Received" or [Status After Delimiter]="Closed due to Inactivity" or [Status After Delimiter]="Handed over to a Project" or [Status After Delimiter]="Handed Over from CBS" or [Status After Delimiter]="Handed Over from Business Serv" or [Status After Delimiter]="Handed over from Customer " or [Status After Delimiter]="Customer Eng." or [Status After Delimiter]="Handed over from Group Risk" or [Status After Delimiter]="H" or [Status After Delimiter]="Handed over from IAdmin" or [Status After Delimiter]="Handed over from Mort" or [Status After Delimiter]="Risk" or [Status After Delimiter]="WI" or [Status After Delimiter]="Not for now" or [Status After Delimiter]= "Closed due to inactivity") then "Closed" else "")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Infligh", each (if ( [Status After Delimiter] = "Acknowledged" or [Status After Delimiter] = "Under Investigation" or [Status After Delimiter] = "Implementing" or [Status After Delimiter] = "On hold" or [Status After Delimiter] = "" ) then"Inflight" else "")),
#"Inserted Year" = Table.AddColumn(#"Added Custom4", "Year", each Date.Year([#"DATE (YYYY-MM-DD)"]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([#"DATE (YYYY-MM-DD)"]), Int64.Type),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Month", "Quarter", each if [Month] >= 10 then "Q1" else if [Month] <= 3 then "Q2" else if [Month] <= 6 then "Q3" else "Q4"),
#"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each ([Month] = 3 or [Month] = 6 or [Month] = 8 or [Month] = 10) and ([Quarter] = "Q2")),
#"Added Custom5" = Table.AddColumn(#"Filtered Rows1", "Fiscal Year", each if[Month] < 10 then
Text.End(Number.ToText([Year]),4) else
Text.End(Number.ToText([Year]-1),4)),
#"Filtered Rows3" = Table.SelectRows(#"Added Custom5", each true),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows3", each true),
#"Added Custom6" = Table.AddColumn(#"Filtered Rows2", "TMonth", each Date.ToText([#"DATE (YYYY-MM-DD)"],"MMM-yy")),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Implemente", each (if ([Status After Delimiter]="Implemented" ) then "Implemented" else ""))
in
#"Removed Columns1"
