Hi All,
Could you please help, i have a problem with the code and not sure if i got it correct.
let
Source = JobsPerformed,
#"Filtered Rows" = Table.SelectRows(Source, each [Ticket Number] <> "" or [Ticket Number] <> null),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows", {{"Veeva #(lf)Channel", "Veeva Channel"}, {"Global/#(lf)Local", "Global_or_Local"}, {"Global#(lf)Master Year", "Global Master Year"}, {"Total No. of #(lf)Slides in GM", "Total Number of Slides within GM"}, {"No. of Global Slides #(lf)in localised version", "Number of Global Slides with localised version"}, {"% of Global slides in#(lf)Localised version", "Percentage of Global slides with Localised version"}, {"No of Local #(lf)Slides(if any)", "Number of Local Slides"}, {"Date of #(lf)Approval", "Date of Approval"}, {"Actual #(lf)Receiving Date", "Actual Receiving Date"}, {"Designer #(lf)Engg Min", "Designer Engg Min"}, {"Main Folder #(lf)(Directory)", "Main Folder"}, {"Sub Folder #(lf)(Sub-Directory)", "Sub Folder"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Draft", "Draft - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","Draft_","",Replacer.ReplaceText,{"Draft - Copy"}),
#"Renamed Columns1" = Table.RenameColumns(#"Replaced Value",{{"Draft - Copy", "DraftNumber"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns1","",null,Replacer.ReplaceValue,{"Draft"}),
#"Filled Down" = Table.FillDown(#"Replaced Value1",{"Draft"}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Filled Down", "Text After Delimiter", each Text.AfterDelimiter([Draft], "_"), type text),
#"Grouped Rows" = Table.Group(#"Inserted Text After Delimiter", {"Ticket Number"}, {{"TicketNumber.1", each _, type table [Ticket Number=nullable text, Draft=nullable text, Veeva Channel=nullable text, Market=nullable text, Market Contact=nullable text, Category=nullable text, Channel=nullable text, Brand=nullable text, Sub Brand=nullable text, Global_or_Local=nullable text, Name of the Global Master=nullable text, Global Master Year=any, Total Number of Slides within GM=any, Number of Global Slides with localised version=any, Percentage of Global slides with Localised version=any, Number of Local Slides=any, Zinc Code=nullable text, Presentation Name on iRep=nullable text, Job Type=nullable text, Job Type for SLA Calulation=nullable text, SLA as per Job Type=number, Date of Approval=nullable date, Status=nullable text, Actual Receiving Date=nullable date, DC Review start date=nullable date, DC Review end date=nullable date, DC Start Date=nullable date, Delivery Date=nullable date, Content Launch Date=nullable date, TAT=nullable number, #"TAT Met Yes/No"=nullable text, Designer Name=nullable text, Designer Engg Min=nullable number, Slide Count=nullable number, Main Folder=nullable text, Sub Folder=nullable text, Comments=nullable text, Assigned By=any, MT Version=any, DraftNumber=nullable text, Text After Delimiter=text]},{"max", each List.Max([Text After Delimiter]), type text}}),
#"Expanded TicketNumber.1" = Table.ExpandTableColumn(#"Grouped Rows", "TicketNumber.1", {"Ticket Number", "Draft", "Veeva Channel", "Market", "Market Contact", "Category", "Channel", "Brand", "Sub Brand", "Global_or_Local", "Name of the Global Master", "Global Master Year", "Total Number of Slides within GM", "Number of Global Slides with localised version", "Percentage of Global slides with Localised version", "Number of Local Slides", "Zinc Code", "Presentation Name on iRep", "Job Type", "Job Type for SLA Calulation", "SLA as per Job Type", "Date of Approval", "Status", "Actual Receiving Date", "DC Review start date", "DC Review end date", "DC Start Date", "Delivery Date", "Content Launch Date", "TAT", "TAT Met Yes/No", "Designer Name", "Designer Engg Min", "Slide Count", "Main Folder", "Sub Folder", "Comments", "Assigned By", "MT Version", "DraftNumber", "Text After Delimiter"}, {"Ticket Number.1", "Draft", "Veeva Channel", "Market", "Market Contact", "Category", "Channel", "Brand", "Sub Brand", "Global_or_Local", "Name of the Global Master", "Global Master Year", "Total Number of Slides within GM", "Number of Global Slides with localised version", "Percentage of Global slides with Localised version", "Number of Local Slides", "Zinc Code", "Presentation Name on iRep", "Job Type", "Job Type for SLA Calulation", "SLA as per Job Type", "Date of Approval", "Status", "Actual Receiving Date", "DC Review start date", "DC Review end date", "DC Start Date", "Delivery Date", "Content Launch Date", "TAT", "TAT Met Yes/No", "Designer Name", "Designer Engg Min", "Slide Count", "Main Folder", "Sub Folder", "Comments", "Assigned By", "MT Version", "DraftNumber", "Text After Delimiter"})
#"Filtered Rows1" = Table.SelectRows(#"Expanded TicketNumber.1", each [Text After Delimiter] = [max])
in
#"Filtered Rows1"
