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

Keflyn

New Member
Joined
Apr 19, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • FinShift.png
    FinShift.png
    21.3 KB · Views: 23
  • FlexShift.png
    FlexShift.png
    20.2 KB · Views: 23
  • Lcltable.png
    Lcltable.png
    59.3 KB · Views: 25

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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?
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top