Power Query or Excel taking a value and determining which shift it goes on.

[Keflyn]

I have been scouring the internet for a good answer for this but with my skill level I haven't been able to piece together parts to make it work. I am pretty good at pulling and filtering data to get what I need, but these nested ifs and looking at different tables with it are complex to me.

I have a query built that pulls a table in from an SQL server. One of the columns LplTyp has a value of WIP or RAW. I need to build a function that looks at the TrnDTM.2 column which has a time value (already separated the date out) and if it is WIP, look at the time and determine which shift it happened on and return back either a 1, 2 or 3 using the query FinShift. If LplType is RAW, I need it to use a different set of shift times, which I have in a query called FlexShift.

The end goal is to have the first table and a different table work together in a pivot chart and group the items by a shop order # and the shift, which both tables will have once the above is coded into a new column. I think if I can get the shifts on the lcltable, then I can either do a merge or some other way to compare the total pieces on the un-named table to the pieces consumed on the Lcltable.

Any ideas out there?

 

[CSmith]

Not totally sure what you're asking for, but if I'm understanding your request correctly, why not join the tables together with left outer joins creating your Fin_Shift and FlexShift columns? Then you can merge the two columns using WIP and RAW selecting the proper value from the new columns based on the other column value?