I’ll try to explain this as best as possible but it’s pretty complex so it will likely end up being easier to share the file.

I have a big table that’s data from multiple sheets using power query.

In the big table there’s a column called “session time” which should be either AM or PM.

What I need to do is add a column to the big table that’s a unique ID formed by concatenating the date and session time (time is currently empty in this table)

Then use this unique ID in a lookup formula for the session time in another table to auto populate the session time in the big table.

Only problem is... the unique ID is dependent on the session time being there, and I can’t get the session time without using the unique ID.

I need the AM or PM in the unique ID because there will be sometimes two sessions a day with the only differing thing being the time.

I’m a month out from needing this working for my thesis data collection and I feel like I’m in way over my head. I’ve spent all day trying to get this right and can’t.

Extremely happy to share the file and very grateful for any assistance.

Edit: Here is a onedrive link to the file, hopefully it works!

Https://1drv.ms/u/s!AgCtXoDfrpXWuWa2N2YncfAHR-iG?e=xQzTnN

Edit edit: I think I may have found a way around the unique identifier. I used =[@[Start Time]]<0.5, “AM”, “PM”)

Not quite sure why this works but it seems to do the trick