Forum Discussion
Power Query - Duplicate Header Row
Hi, My power query is sourcing data from a FOLDER with 2 csv files in it. After importing and editing data, then added to PowerPivot, I noticed a duplicate HEADER row at the bottom of the PowerPi...
Hi qsmith83,
I change M code like below
let Source = Csv.Document(#"Sample File Parameter1",[Delimiter=",", Columns=50, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Replaced Value" = Table.ReplaceValue(#"Removed Blank Rows","""""","",Replacer.ReplaceText,{"Column1"}), #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TICKET_NBR", type text}, {"CUST_NAME", type text}, {"CUST_CODE", Int64.Type}, {"ASSIGNED_REGION", type text}, {"ASSIGNED_BRANCH", type text}, {"ASSIGNED_DEPOT", type text}, {"ASSIGNED_FLEET", type text}, {"ASSIGNED_RUN_NO", type text}, {"ASSIGNED_RUN_DESC", type text}, {"JOB_BOOKING_DATETIME", type datetime}, {"ATTEMPTED_ACCEPTANCE_REGION", type text}, {"ATTEMPTED_ACCEPTANCE_BRANCH", type text}, {"ATTEMPTED_ACCEPTANCE_DEPOT", type text}, {"ATTEMPTED_ACCEPTANCE_FLEET", type text}, {"ATTEMPTED_ACCEPTANCE_RUN_NO", type text}, {"ATTEMPTED_ACCEPTANCE_RUN_DESC", type text}, {"ATTEMPTED_ACCEPTANCE_DATETIME", type datetime}, {"ATTEMPTED_ACCEPTANCE_ELAPSED_T", type text}, {"ACCEPTANCE_REGION", type text}, {"ACCEPTANCE_BRANCH", type text}, {"ACCEPTANCE_DEPOT", type text}, {"ACCEPTANCE_FLEET", type text}, {"ACCEPTANCE_RUN_NO", type text}, {"ACCEPTANCE_RUN_DESC", type text}, {"ACCEPTANCE_DATETIME", type datetime}, {"ACCEPTANCE_ELAPSED_TIME", type text}, {"OFD_REGION", type text}, {"OFD_BRANCH", type text}, {"OFD_DEPOT", type text}, {"OFD_FLEET", type text}, {"OFD_RUN_NO", type text}, {"OFD_RUN_DESC", type text}, {"OFD_DATETIME", type datetime}, {"OFD_ELAPSED_TIME", type text}, {"ATTEMPTED_DELIVERY_REGION", type text}, {"ATTEMPTED_DELIVERY_BRANCH", type text}, {"ATTEMPTED_DELIVERY_DEPOT", type text}, {"ATTEMPTED_DELIVERY_FLEET", type text}, {"ATTEMPTED_DELIVERY_RUN_NO", type text}, {"ATTEMPTED_DELIVERY_RUN_DESC", type text}, {"ATTEMPTED_DELIVERY_DATETIME", type datetime}, {"ATTEMPTED_DELIVERY_ELAPSED_TIM", type text}, {"DELIVERY_REGION", type text}, {"DELIVERY_BRANCH", type text}, {"DELIVERY_DEPOT", type text}, {"DELIVERY_FLEET", type text}, {"DELIVERY_RUN_NO", type text}, {"DELIVERY_RUN_DESC", type text}, {"DELIVERY_DATETIME", type datetime}, {"DELIVERY_ELAPSED_TIME", type text}}) in #"Changed Type"let Source = Folder.Files("C:\Users\Administrator\Downloads\OneDrive_1_10-18-2019"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Raw Data", each #"Transform File from Raw Data"([Content])), #"Renamed Columnss1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columnss1" = Table.SelectColumns(#"Renamed Columnss1", {"Source.Name", "Transform File from Raw Data"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columnss1", "Transform File from Raw Data", Table.ColumnNames(#"Transform File from Raw Data"(#"Sample File"))), //#"Changed Typpe" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}}), #"Removed Columnss" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"}), #"Removed Blank Rows" = Table.SelectRows(#"Removed Columnss", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), // #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"TICKET_NBR", type text}, {"CUST_NAME", type text}, {"CUST_CODE", Int64.Type}, {"ASSIGNED_REGION", type text}, {"ASSIGNED_BRANCH", type text}, {"ASSIGNED_DEPOT", type text}, {"ASSIGNED_FLEET", type text}, {"ASSIGNED_RUN_NO", type text}, {"ASSIGNED_RUN_DESC", type text}, {"JOB_BOOKING_DATETIME", type datetime}, {"ATTEMPTED_ACCEPTANCE_REGION", type text}, {"ATTEMPTED_ACCEPTANCE_BRANCH", type text}, {"ATTEMPTED_ACCEPTANCE_DEPOT", type text}, {"ATTEMPTED_ACCEPTANCE_FLEET", type text}, {"ATTEMPTED_ACCEPTANCE_RUN_NO", type text}, {"ATTEMPTED_ACCEPTANCE_RUN_DESC", type text}, {"ATTEMPTED_ACCEPTANCE_DATETIME", type datetime}, {"ATTEMPTED_ACCEPTANCE_ELAPSED_T", type text}, {"ACCEPTANCE_REGION", type text}, {"ACCEPTANCE_BRANCH", type text}, {"ACCEPTANCE_DEPOT", type text}, {"ACCEPTANCE_FLEET", type text}, {"ACCEPTANCE_RUN_NO", type text}, {"ACCEPTANCE_RUN_DESC", type text}, {"ACCEPTANCE_DATETIME", type datetime}, {"ACCEPTANCE_ELAPSED_TIME", type text}, {"OFD_REGION", type text}, {"OFD_BRANCH", type text}, {"OFD_DEPOT", type text}, {"OFD_FLEET", type text}, {"OFD_RUN_NO", type text}, {"OFD_RUN_DESC", type text}, {"OFD_DATETIME", type datetime}, {"OFD_ELAPSED_TIME", type text}, {"ATTEMPTED_DELIVERY_REGION", type text}, {"ATTEMPTED_DELIVERY_BRANCH", type text}, {"ATTEMPTED_DELIVERY_DEPOT", type text}, {"ATTEMPTED_DELIVERY_FLEET", type text}, {"ATTEMPTED_DELIVERY_RUN_NO", type text}, {"ATTEMPTED_DELIVERY_RUN_DESC", type text}, {"ATTEMPTED_DELIVERY_DATETIME", type datetime}, {"ATTEMPTED_DELIVERY_ELAPSED_TIM", type text}, {"DELIVERY_REGION", type text}, {"DELIVERY_BRANCH", type text}, {"DELIVERY_DEPOT", type text}, {"DELIVERY_FLEET", type text}, {"DELIVERY_RUN_NO", type text}, {"DELIVERY_RUN_DESC", type text}, {"DELIVERY_DATETIME", type datetime}, {"DELIVERY_ELAPSED_TIME", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ASSIGNED_REGION", "ASSIGNED_BRANCH", "ASSIGNED_DEPOT", "ASSIGNED_FLEET", "ASSIGNED_RUN_NO", "ASSIGNED_RUN_DESC"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "ATTEMPTED_ACCEPTANCE_ELAPSED_T", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ATTEMPTED_ACCEPTANCE_ELAPSED_T.1", "ATTEMPTED_ACCEPTANCE_ELAPSED_T.2", "ATTEMPTED_ACCEPTANCE_ELAPSED_T.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ATTEMPTED_ACCEPTANCE_ELAPSED_T.1", Int64.Type}, {"ATTEMPTED_ACCEPTANCE_ELAPSED_T.2", Int64.Type}, {"ATTEMPTED_ACCEPTANCE_ELAPSED_T.3", Int64.Type}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"ATTEMPTED_ACCEPTANCE_ELAPSED_T.2", "ATTEMPTED_ACCEPTANCE_ELAPSED_T.3"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"ATTEMPTED_ACCEPTANCE_ELAPSED_T.1", "JOB_TO_ATT_ACCEP_ELAPSED_TIME (HRS)"}, {"ACCEPTANCE_ELAPSED_TIME", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS)"}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS)", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).1", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).2", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).3"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).1", Int64.Type}, {"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).2", Int64.Type}, {"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).3", Int64.Type}}), #"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).2", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).3"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).1", "JOB_TO_ACCEP_ELAPSED_TIME (HRS)"}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns1", "OFD_ELAPSED_TIME", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"OFD_ELAPSED_TIME.1", "OFD_ELAPSED_TIME.2", "OFD_ELAPSED_TIME.3"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"OFD_ELAPSED_TIME.1", Int64.Type}, {"OFD_ELAPSED_TIME.2", Int64.Type}, {"OFD_ELAPSED_TIME.3", Int64.Type}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type4",{{"OFD_ELAPSED_TIME.1", "JOB_TO_OFD_ELAPSED_TIME"}}), #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"OFD_ELAPSED_TIME.2", "OFD_ELAPSED_TIME.3"}), #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"JOB_TO_ACCEP_ELAPSED_TIME (HRS)", "JOB_TO_ACCEP_ELAPSED_TIME"}, {"JOB_TO_ATT_ACCEP_ELAPSED_TIME (HRS)", "JOB_TO_ATT_ACCEP_ELAPSED_TIME"}, {"ATTEMPTED_DELIVERY_ELAPSED_TIM", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME"}}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Renamed Columns3", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.1", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.2", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.3"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.1", Int64.Type}, {"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.2", Int64.Type}, {"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.3", Int64.Type}}), #"Removed Columns4" = Table.RemoveColumns(#"Changed Type5",{"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.2", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.3"}), #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.1", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME"}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns4", "DELIVERY_ELAPSED_TIME", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"DELIVERY_ELAPSED_TIME.1", "DELIVERY_ELAPSED_TIME.2", "DELIVERY_ELAPSED_TIME.3"}), #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"DELIVERY_ELAPSED_TIME.1", Int64.Type}, {"DELIVERY_ELAPSED_TIME.2", Int64.Type}, {"DELIVERY_ELAPSED_TIME.3", Int64.Type}}), #"Renamed Columns5" = Table.RenameColumns(#"Changed Type6",{{"DELIVERY_ELAPSED_TIME.1", "JOB_TO_DELIVERY_ELAPSED_TIME"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns5", "Accep_To_Delivery_ET", each Number.RoundDown(Duration.Days(Duration.From([DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))) * 24+ Duration.Hours(Duration.From([DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Removed Columns5" = Table.RemoveColumns(#"Added Custom1",{"DELIVERY_ELAPSED_TIME.2", "DELIVERY_ELAPSED_TIME.3"}), #"Added Custom" = Table.AddColumn(#"Removed Columns5", "Acceptance To Att Delivery", each Number.RoundDown(Duration.Days(Duration.From([ATTEMPTED_DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))) * 24+ Duration.Hours(Duration.From([ATTEMPTED_DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Renamed Columns6" = Table.RenameColumns(#"Added Custom",{{"Accep_To_Delivery_ET", "AccepToDeliveryET"}, {"Acceptance To Att Delivery", "AccepToAttDeliveryET"}}), #"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns6",{{"AccepToDeliveryET", Int64.Type}, {"AccepToAttDeliveryET", Int64.Type}}), #"Renamed Columns7" = Table.RenameColumns(#"Changed Type7",{{"AccepToAttDeliveryET", "AccepToAttDeliveryElapsedTime Hrs"}, {"AccepToDeliveryET", "AccepToDeliveryElapsedTime Hrs"}}), #"Added Custom2" = Table.AddColumn(#"Renamed Columns7", "AcceptoAttDeliveryET Days", each Duration.Days(Duration.From([ATTEMPTED_DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Renamed Columns8" = Table.RenameColumns(#"Added Custom2",{{"AccepToAttDeliveryElapsedTime Hrs", "AccepToAttDeliveryET Hrs"}}), #"Added Custom3" = Table.AddColumn(#"Renamed Columns8", "AccepToDeliveryET Days", each Duration.Days(Duration.From([DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "JobToAttAccep Days", each Duration.Days(Duration.From([ATTEMPTED_ACCEPTANCE_DATETIME]-[JOB_BOOKING_DATETIME]))), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "JobToAccep Days", each Duration.Days(Duration.From([ACCEPTANCE_DATETIME]-[JOB_BOOKING_DATETIME]))) in #"Added Custom5"You could try it to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dax perfect, it worked. Thank you, much appreciated:)
dax
Community Support
Community SupportHi qsmith83,
I can't based on your M code to reproduce your problem, so if possible, could you please inform me more details(such as your sample data, your current output and expeced output), you could use other information to replace your data in csv file ? In addition, you also could check applied step one by one to see when will generate duplicated row header.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi dax ,
Here is link to data source files
https://drive.google.com/file/d/1nQQT7n0L9nY764p2-8e4h8OaCTTqRLTa/view?usp=sharing
Also here is image of duplicate row in PowerPivot table
HI dax ,
Were you able to replicate the issue based on the uploaded data that I provided? Hope to hear from you soon.
Thanks.
- dax
Hi qsmith83,
I change your sample like below(add "promoted Header" in below Mode)
let Source = Csv.Document(#"Sample File Parameter1",[Delimiter=",", Columns=50, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"Then change your M code like below
let Source = Folder.Files("C:\Users\Administrator\Downloads\OneDrive_1_10-18-2019"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Raw Data", each #"Transform File from Raw Data"([Content])), #"Renamed Columnss1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columnss1" = Table.SelectColumns(#"Renamed Columnss1", {"Source.Name", "Transform File from Raw Data"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columnss1", "Transform File from Raw Data", Table.ColumnNames(#"Transform File from Raw Data"(#"Sample File"))), #"Changed Typpe" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}, {"_5", type text}, {"_6", type text}, {"_7", type text}, {"_8", type text}, {"_9", type text}, {"_10", type text}, {"_11", type text}, {"_12", type text}, {"_13", type text}, {"_14", type text}, {"_15", type text}, {"_16", type text}, {"_17", type text}, {"_18", type text}, {"_19", type text}, {"_20", type text},{"_21", type text}, {"_22", type text}, {"_23", type text}, {"_24", type text}, {"_25", type text}, {"_26", type text}, {"_27", type text}, {"_28", type text}, {"_29", type text}, {"_30", type text}, {"_31", type text}, {"_32", type text}, {"_33", type text}, {"_34", type text}, {"_35", type text}, {"_36", type text}, {"_37", type text}, {"_38", type text}, {"_39", type text}, {"_40", type text}, {"_41", type text}, {"_42", type text}, {"_43", type text}, {"_44", type text}, {"_45", type text}, {"_46", type text}, {"_47", type text}, {"_48", type text}, {"_49", type text}}), #"Removed Columnss" = Table.RemoveColumns(#"Changed Typpe",{"Source.Name"}), #"Removed Blank Rows" = Table.SelectRows(#"Removed Columnss", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"TICKET_NBR", type text}, {"CUST_NAME", type text}, {"CUST_CODE", Int64.Type}, {"ASSIGNED_REGION", type text}, {"ASSIGNED_BRANCH", type text}, {"ASSIGNED_DEPOT", type text}, {"ASSIGNED_FLEET", type text}, {"ASSIGNED_RUN_NO", type text}, {"ASSIGNED_RUN_DESC", type text}, {"JOB_BOOKING_DATETIME", type datetime}, {"ATTEMPTED_ACCEPTANCE_REGION", type text}, {"ATTEMPTED_ACCEPTANCE_BRANCH", type text}, {"ATTEMPTED_ACCEPTANCE_DEPOT", type text}, {"ATTEMPTED_ACCEPTANCE_FLEET", type text}, {"ATTEMPTED_ACCEPTANCE_RUN_NO", type text}, {"ATTEMPTED_ACCEPTANCE_RUN_DESC", type text}, {"ATTEMPTED_ACCEPTANCE_DATETIME", type datetime}, {"ATTEMPTED_ACCEPTANCE_ELAPSED_T", type text}, {"ACCEPTANCE_REGION", type text}, {"ACCEPTANCE_BRANCH", type text}, {"ACCEPTANCE_DEPOT", type text}, {"ACCEPTANCE_FLEET", type text}, {"ACCEPTANCE_RUN_NO", type text}, {"ACCEPTANCE_RUN_DESC", type text}, {"ACCEPTANCE_DATETIME", type datetime}, {"ACCEPTANCE_ELAPSED_TIME", type text}, {"OFD_REGION", type text}, {"OFD_BRANCH", type text}, {"OFD_DEPOT", type text}, {"OFD_FLEET", type text}, {"OFD_RUN_NO", type text}, {"OFD_RUN_DESC", type text}, {"OFD_DATETIME", type datetime}, {"OFD_ELAPSED_TIME", type text}, {"ATTEMPTED_DELIVERY_REGION", type text}, {"ATTEMPTED_DELIVERY_BRANCH", type text}, {"ATTEMPTED_DELIVERY_DEPOT", type text}, {"ATTEMPTED_DELIVERY_FLEET", type text}, {"ATTEMPTED_DELIVERY_RUN_NO", type text}, {"ATTEMPTED_DELIVERY_RUN_DESC", type text}, {"ATTEMPTED_DELIVERY_DATETIME", type datetime}, {"ATTEMPTED_DELIVERY_ELAPSED_TIM", type text}, {"DELIVERY_REGION", type text}, {"DELIVERY_BRANCH", type text}, {"DELIVERY_DEPOT", type text}, {"DELIVERY_FLEET", type text}, {"DELIVERY_RUN_NO", type text}, {"DELIVERY_RUN_DESC", type text}, {"DELIVERY_DATETIME", type datetime}, {"DELIVERY_ELAPSED_TIME", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ASSIGNED_REGION", "ASSIGNED_BRANCH", "ASSIGNED_DEPOT", "ASSIGNED_FLEET", "ASSIGNED_RUN_NO", "ASSIGNED_RUN_DESC"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "ATTEMPTED_ACCEPTANCE_ELAPSED_T", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ATTEMPTED_ACCEPTANCE_ELAPSED_T.1", "ATTEMPTED_ACCEPTANCE_ELAPSED_T.2", "ATTEMPTED_ACCEPTANCE_ELAPSED_T.3"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ATTEMPTED_ACCEPTANCE_ELAPSED_T.1", Int64.Type}, {"ATTEMPTED_ACCEPTANCE_ELAPSED_T.2", Int64.Type}, {"ATTEMPTED_ACCEPTANCE_ELAPSED_T.3", Int64.Type}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"ATTEMPTED_ACCEPTANCE_ELAPSED_T.2", "ATTEMPTED_ACCEPTANCE_ELAPSED_T.3"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"ATTEMPTED_ACCEPTANCE_ELAPSED_T.1", "JOB_TO_ATT_ACCEP_ELAPSED_TIME (HRS)"}, {"ACCEPTANCE_ELAPSED_TIME", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS)"}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS)", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).1", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).2", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).3"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).1", Int64.Type}, {"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).2", Int64.Type}, {"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).3", Int64.Type}}), #"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).2", "ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).3"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"ATT_ACCEP_TO_ACCEP_ELAPSED_TIME (HRS).1", "JOB_TO_ACCEP_ELAPSED_TIME (HRS)"}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns1", "OFD_ELAPSED_TIME", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"OFD_ELAPSED_TIME.1", "OFD_ELAPSED_TIME.2", "OFD_ELAPSED_TIME.3"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"OFD_ELAPSED_TIME.1", Int64.Type}, {"OFD_ELAPSED_TIME.2", Int64.Type}, {"OFD_ELAPSED_TIME.3", Int64.Type}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type4",{{"OFD_ELAPSED_TIME.1", "JOB_TO_OFD_ELAPSED_TIME"}}), #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"OFD_ELAPSED_TIME.2", "OFD_ELAPSED_TIME.3"}), #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"JOB_TO_ACCEP_ELAPSED_TIME (HRS)", "JOB_TO_ACCEP_ELAPSED_TIME"}, {"JOB_TO_ATT_ACCEP_ELAPSED_TIME (HRS)", "JOB_TO_ATT_ACCEP_ELAPSED_TIME"}, {"ATTEMPTED_DELIVERY_ELAPSED_TIM", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME"}}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Renamed Columns3", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.1", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.2", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.3"}), #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.1", Int64.Type}, {"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.2", Int64.Type}, {"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.3", Int64.Type}}), #"Removed Columns4" = Table.RemoveColumns(#"Changed Type5",{"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.2", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.3"}), #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME.1", "JOB_TO_ATTEMPTED_DELIVERY_ELAPSED_TIME"}}), #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns4", "DELIVERY_ELAPSED_TIME", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"DELIVERY_ELAPSED_TIME.1", "DELIVERY_ELAPSED_TIME.2", "DELIVERY_ELAPSED_TIME.3"}), #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"DELIVERY_ELAPSED_TIME.1", Int64.Type}, {"DELIVERY_ELAPSED_TIME.2", Int64.Type}, {"DELIVERY_ELAPSED_TIME.3", Int64.Type}}), #"Renamed Columns5" = Table.RenameColumns(#"Changed Type6",{{"DELIVERY_ELAPSED_TIME.1", "JOB_TO_DELIVERY_ELAPSED_TIME"}}), #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns5",{"DELIVERY_ELAPSED_TIME.2", "DELIVERY_ELAPSED_TIME.3"}), #"Added Custom" = Table.AddColumn(#"Removed Columns5", "Acceptance To Att Delivery", each Number.RoundDown(Duration.Days(Duration.From([ATTEMPTED_DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))) * 24+ Duration.Hours(Duration.From([ATTEMPTED_DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Accep_To_Delivery_ET", each Number.RoundDown(Duration.Days(Duration.From([DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))) * 24+ Duration.Hours(Duration.From([DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Renamed Columns6" = Table.RenameColumns(#"Added Custom1",{{"Accep_To_Delivery_ET", "AccepToDeliveryET"}, {"Acceptance To Att Delivery", "AccepToAttDeliveryET"}}), #"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns6",{{"AccepToDeliveryET", Int64.Type}, {"AccepToAttDeliveryET", Int64.Type}}), #"Renamed Columns7" = Table.RenameColumns(#"Changed Type7",{{"AccepToAttDeliveryET", "AccepToAttDeliveryElapsedTime Hrs"}, {"AccepToDeliveryET", "AccepToDeliveryElapsedTime Hrs"}}), #"Added Custom2" = Table.AddColumn(#"Renamed Columns7", "AcceptoAttDeliveryET Days", each Duration.Days(Duration.From([ATTEMPTED_DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Renamed Columns8" = Table.RenameColumns(#"Added Custom2",{{"AccepToAttDeliveryElapsedTime Hrs", "AccepToAttDeliveryET Hrs"}}), #"Added Custom3" = Table.AddColumn(#"Renamed Columns8", "AccepToDeliveryET Days", each Duration.Days(Duration.From([DELIVERY_DATETIME]-[ACCEPTANCE_DATETIME]))), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "JobToAttAccep Days", each Duration.Days(Duration.From([ATTEMPTED_ACCEPTANCE_DATETIME]-[JOB_BOOKING_DATETIME]))), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "JobToAccep Days", each Duration.Days(Duration.From([ACCEPTANCE_DATETIME]-[JOB_BOOKING_DATETIME]))) in #"Added Custom5"You could try this to see whether it work or not. You also could refer to this post for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.