Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Power Query - Duplicate Header Row

‎10-16-2019 06:01 PM

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 PowerPivot table. Double checked the source data files and no duplicate row found there. 

 

I'm thinking it could be the APPLIED STEPS in the power query but couldn't work out what the issue was. Link to Advanced Editor query with Applied Steps here >> https://drive.google.com/file/d/1dLotcnnDA24INTlsqiAnHXAkj3rtwuIz/view?usp=sharing

 

Appreciate any help, thanks. 

Labels:
Message 1 of 9
4,114 Views
0
2 ACCEPTED SOLUTIONS
dax
Community Support
Community Support
In response to Anonymous

‎10-23-2019 01:48 AM

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"

440.PNG

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.

 

View solution in original post

Message 8 of 9
3,891 Views
0
Anonymous
Not applicable
In response to dax

‎10-23-2019 02:14 AM

@dax perfect, it worked. Thank you, much appreciated:)

View solution in original post

Message 9 of 9
3,889 Views
0
8 REPLIES 8
dax
Community Support
Community Support

‎10-17-2019 07:02 PM

Hi 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.

Message 2 of 9
4,058 Views
0
Anonymous
Not applicable
In response to dax

‎10-17-2019 07:41 PM

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

powerpivot duplicate row.PNG

Message 3 of 9
4,055 Views
0
Anonymous
Not applicable
In response to Anonymous

‎10-20-2019 04:27 PM

HI @dax ,

 

Were you able to replicate the issue based on the uploaded data that I provided? Hope to hear from you soon. 

 

Thanks.

Message 4 of 9
4,018 Views
0
dax
Community Support
Community Support
In response to Anonymous

‎10-21-2019 01:51 AM

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"

431.PNG432.PNG

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 5 of 9
4,002 Views
0
Anonymous
Not applicable
In response to dax

‎10-21-2019 03:29 PM

Thanks, it worked HOWEVER after refreshing powerpivot, only one month data showing i.e. just the 'Aug' csv file instead of both csv files Aug & Sep. Please advise.

Message 6 of 9
3,984 Views
0
Anonymous
Not applicable
In response to Anonymous

‎10-22-2019 03:53 AM

Hi @dax ,

 

Based on your last reply, I made changes as you suggested but PowerPivot only showing Aug data and not both dataset from folder i.e. Aug & Sep data. Below screenshots of the changes I made. 

 

Please let me know which change I need to make and where I need to make change. Sorry for the trouble.

 

 

Capture.PNGCapture1.PNGCapture2.PNG

 

Message 7 of 9
3,943 Views
0
dax
Community Support
Community Support
In response to Anonymous

‎10-23-2019 01:48 AM

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"

440.PNG

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.

 

Message 8 of 9
3,892 Views
0
Anonymous
Not applicable
In response to dax

‎10-23-2019 02:14 AM

@dax perfect, it worked. Thank you, much appreciated:)

Message 9 of 9
3,890 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All