Hello,
I'm sorry to start this before having ended all training (that I actually signed up for), but I'm being put in an impossible situation and I'm sort of at a dead end.
in December I inherited a calendar that was supposed to be used to track (mostly to give an overview, nothing HR related) all the facility's employee.
It was not only overcomplicated, but difficult to deal with. I had to kill it. But a replacement is needed... YESTERDAY (sigh!).
So here's what I have:
- table where employees enter their absences (name, from / to dates and type of absence)
- Table to assign a number to each type of absence
- Table with all year dates in every possible format (months names can be tricky)
- Table with bank holidays and bridge days
- Table for organigram
I put all tables in the data model and now I'm stuck on how to create some connections (I guess only through time and exercise I will start coming up with ideas...).
Ideally, but not necessarily, I would keep the data input file and the output one separate. Most important thing is that there has to be no unsupported feature that would prevent from editing in browser or Teams (so no VBA nor macros).
I'm attaching here the inherited file (which by the way is bugged, but just to give an idea of what the pivot should look like) and the new one, as far as I could get.
The original idea was that different days events (absence, weekend, bridge and so on) would be in the pivot as numbers or code, that we could then apply conditional formatting to (any more functional or practical idea will be most welcome). It should be a visual by group (department)
I just don't know how to connect the dots 
Will very much appreciate any form of help.
Edit: seems like the I cannot upload the output file. I'll insert a picture in a tab of my file.
Hi Rossana,
Please complete session 6 of the Power Pivot course. This will set you up to create the relationships. If you're still stuck after that, come back with your file to show your attempts and we can help you from there.
Tip 1: I think the Public Holidays table should have two columns:
- Date
- Type i.e. Bank Holiday or Bridge Half
You shouldn't have two columns containing dates unless those dates are related to one another. I'm pretty sure the bank holiday on Jan 1 isn't related to the Bridge Half on April 1.
Tip 2: Remove the AbsenceCode column from the Absence List table as you don't need it there. Instead, simply create a relationship between the AbsenceList and Absence tables.
Tip 3: Fix your date data types in the AbsenceList. They are currently Text and should be Date. You should do this in Power Query before loading to Power Pivot.
Tip 4: Mark the CalendarData table as the 'Date Table'. This is covered in session 3.09 of the Power Pivot course.
Mynda
After following your advices and studying a little more, I managed to make it work 
Now my problem is with the connection: the source file is stored in an online sharepoint site, and since it belongs to the company I work for, anonymous access is not allowed.
I created the query from "excel file" as source (tried "web" initially).
I tried all combinations by choosing either Public or Organizational for access and then Windows and Organizational account for credentials.
Works for me, but all other users keep getting this error (query is set to refresh on file opening) despite having access to the site with theyr organizational account (which is also the Window account).
I don't know whether I can maybe use the advanced version of the "web" as data source (but I wouldn't know which parameters to put in) or if I can put in a token to for API access...
Any help highly appreciated 
Hi Rossana,
I covered this in a new tutorial added to the Power Query course (2.09). Each user needs to edit the query and enter their own logon credentials to the organizational account before the query can refresh.
Mynda
Unfortunately this is not a possibility I can use (thought about it).
Can I embed a unique key that the source file will respond to?
Nope. SharePoint is controlling access to the file. The only way it can be refreshed by someone is for the person with the file open to verify they are allowed to access the file by entering their logon credentials.
