Lookup & Time Formatted Cells ([h]:mm;@)

Are you still using Excel 2010 or something newer?

It might!

Would you please update your user profile (you could do it like mine to show that you are using two versions).

So, would the users with Excel 2010 have the PowerQuery add-in, and if not, would this be something that they would be able to have installed?

I'm confused - you said 2010 in post #3 ...

Anyway, I will have a look presently (as I am sure others will - if Olly is around, he'll have a PowerQuery solution for you in no time).

PowerQuery isn't ideal if you need backward compatibility with Excel 2010.

Excel 2010 uses older version of PQ engine and will have various issues dealing with connection/query created using Excel 365.
If the connection/query is created on Excel 2010. Excel 365 will be able to open it and refresh, but will have to apply irreversible change before doing so.

However, if there is no need to refresh the data on Excel 2010. Then it should be fine.
Just make sure to avoid data model at all cost, as data model is not backward compatible at all.

With your current table set up. PowerQuery can't directly load data. As it requires data structure more suited for PivotTable and has merged header & multi-level header.

Do you anticipate someone needing to open and work on workbook using older version of Excel? If not I'd recommend combining PQ with PowerPivot to generate the table.

Oh and another question. Does csv get overwritten by export each week? Is there possibility of having individual csv in designated folder that you have access to?
Or is it the table in Excel that gets updated?

Something like attached?

Transformation done on OTC_Export. There are bit more steps than usual as data format did not conform to standard used by PQ (Ex: Duration/Hours).

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

See if this fits your need. Also, if you can clarify what your "Over 304hrs?" means.

To change source csv location. Double click on "OTC_Export" query and double click on "Source" step in Applied steps pane.

You'll need to add record for each employee in Sheet3's table.

Meaning define which segment each employee belongs to.

Once that's done. Go to Data ribbon tool -> Queries & Connections.

Right click on OTC_Export query and select Edit.

Once there, double click on "Source".
0.JPG

Then use "Browse" to find and select the file. Query itself doesn't need any change. As it will automatically update with new data.

See attached mockup - Where I used "UNKNOWN" for all of the employees that wan't in the original sample.

That's because you had some text value mixed in with acceptable time value. If you copy your range and paste as values into some other range...
You should see some values unchanged and some changed to decimal value.

This caused issue/ambiguity in coercion operation in SUM function, and caused erroneous result in R8.

Duration data type is tricky to deal with, and usually does not import well directly from csv file (or between different systems). That's the reason why custom column was added to transform it.

#duration(0,Number.From(Text.Split([Column5],":"){0}),Number.From(Text.Split([Column5],":"){1}),0)

Let me think on best way to add Over 304 hours column.

It just looks complex. If you follow each step in applied query pane of the query editor. You can follow step by step how it's done.
If you have specific question about each of the applied steps, I can explain it in detail for you.

Workable solution is in the workbook I gave you. If csv file structure does not change. It will automatically handle it.

As for Over 304 hours. It's best to keep it separate from Pivot Table. Due to different aggregation level (you could use PowerView but that just adds more complexity).

Formula:

=IF(R5>304/24,304/24-R5,0)

See attached for sample. But this isn't a good sample as non of the employee went over 304 hours.

There's few ways to go about that.

1. Using standard CF, add 3 separate CF with formula.
Excel uses 1 to represent 24 hours (1 day). So 304/24 = 304 hours.
If value in R column > 304/24 -> Color Red
If value in R column = MEDIAN(280/24, Value in R, 300/24)=Value in R -> Color blue
etc.
2. Using Cell based on their values (3 color scale) & one additional for the clear cell.
Make sure to order it from clear cell CF to 3 color scale. And tick "Stop if True" for clear cell CF.

3. Using KPI measure in Pivot Table
This can be done using single measure.

However, your condition excludes some time range or has overlap from post (ex: greater than 300 hrs to less than 304 hrs isn't included in your ranges).
Should it be...
0 to <=250 -> No fill.
>250 to <=280 -> Yellow
etc

Or
0 to < 250 -> No fill.
>= 250 to < 280 -> Yellow
etc?

EDIT: As Ali wrote. It's best to ask it in new thread. If you want me to respond to the new thread. You can PM me with the link to new thread.