Hello Everyone,
I am using microsoft 365 x64 on my windows laptop and I need some help.
1. on the "Asking" attachment file there is a file that call "Report File". in the file i have combine data from 2 different source. and the data updating daily by export from software. how to pick up balance qty on the last date for each PO ?
2. I have employee absenteeism data. the data its export from software. it's possible using power query to pick up the employee name & Ignoring the duplicate data. the data update daily and the Sheet on each day is different . the Sheet represent each facescan ID on one area.
Kind Regard,
Justin R
Hi Justin!
1. I have attached a file where I grouped your rows by PO and product, I found the latest date by sorting by the date and adding an index column then finding the max index number and grabbing that qty. There are probably better ways of doing this but that seemed the easiest to me, hopefully, it at least gets you started on your solution.
2. I'm not understanding your question. Are you only needing each employee listed once ignoring the time? What is it you are looking for? If in your attachment, you could also include a mock-up of what you are looking for, that would be helpful in getting your question answered.
Hi Jessica,
1. I'am sorry, where i can download the attachment file?
2. I need to know each name of worker and their employeeID, without the duplicate data then I need count the number of worker.
Kind Regards,
Justin R
Oops sorry! I must have forgotten to attach the file, my bad. I attached it to this reply.
As for your other question, is the GuestName column the Employee Name? I don't see any other column that would have the employee's name, if so highlight GuestName and EmployeeID and delete duplicates, then when you send your table to Excel turn on the totals and choose count from the drop-down.
If GuestName is not the Employees name, which I assume not, then if you have another file with the employee information you would merge the queries by Employee ID to get the name of the employee with the transaction information expand your tables and the rest of the process would be the same. Hope that made sense. If not, I attached an example and you should be able to follow the steps. Let me know if you have any further questions. 🙂
Hi Jessica,
1. Yes GuessName column are Employee Name.
but when I use the original data an error occurs. please view attachment file for the detail.
2. For the PO & Product test file it's working.
Kind Regards,
Justin R
I'm sorry, Justin, this was so long ago I'm trying to refresh my memory. I think I have most of the pieces, but where are you getting your original data that is creating the issue? Is a mock up of that data in the original zip file attached to your query?
I'm Sorry Jessica. I need time for them data as a whole with a large number of columns compared to mock up but it's solved now.
I have another issue, what if I want to recap the absence rate of each person from the data?
please view attachment file for the reference.
I would send your query to the data model and do a pivot table. Unfortunately, I don't have full access to the data model on my current computer so I just did a calculated column outside of the pivot table; of course, you could just do a calculated measure instead. Hope this helps, I'm glad you were able to get your other issue to work. 🙂
Okay, I'm not good to create calculated measure but let me try.
Thank your for your help. It's help me a lot.
Best wishes for you
