let
Source = Csv.Document(File.Contents("C:\Users\ckanai\Downloads\OTC_Export.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"EmpName"),
#"Added Custom" = Table.AddColumn(#"Merged Columns", "Date", each #date(Number.FromText(Text.Split([Column3],"/"){2}),Number.FromText(Text.Split([Column3],"/"){1}),Number.FromText(Text.Split([Column3],"/"){0}))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column3"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"EmpName", "Date", "Column4", "Column5"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column4", "Type"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Duration", each #duration(0,Number.From(Text.Split([Column5],":"){0}),Number.From(Text.Split([Column5],":"){1}),0)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration", type duration}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Column5"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns1", {"EmpName"}, tblSegment, {"Employee"}, "tblSegment", JoinKind.LeftOuter),
#"Expanded tblSegment" = Table.ExpandTableColumn(#"Merged Queries", "tblSegment", {"Segment"}, {"Segment"})
in
#"Expanded tblSegment"
Also added tblSegment (Employee-Department).
Bookmarks