Power Query - Duplicate Header Row

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 Zhi

If 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:)