Can I do this in PowerQuery and how would i do it

[bearcub]

Hi,

I have to do monthly accruals for patient visits that are entered into a huge database by the doctor's offices. There is always a lag in data entry. I believe they have 60 days to enter the data but I only run the report around the 23 rd of each month. The reports i receive include all the patient visits from the beginning of time so it is a pain to double check to see that all the forecasted visits have been completed and entered into the system.

Last month, for example, we were expecting 200K of patient visit costs for Oct. I ran the Oct report (at the end of Oct) and I had only about 50K of patient costs entered leaving a difference of 150K. i just ran the Nov report and I determined that we had 118K of the 150K in the report, leaving about 38K that will be entered hopefully in Dec. As you can see, I need to create some sort of query or mechanism to easily find out the ones were missing from the prior reports and include them in the current accrual.

Could I use PQ like an Access query that could provide me with a list of prior visits (like Oct) that were appear in the Nov report but were not in the Oct report? I

Can this be done and do you have any tips or know of any youtube vides that could walk through what to do?

Thank you for your he

 

[Ombir]

Post some sample input data and your expected output so that someone can help you easily and where you're storing this data.....in Excel or Access ?

 

[bearcub]

How do I post an Excel file? I would have done but I thought I couldn't attach files for review?

To clarify the example, I could just take a single visit.

If I have a visit in Oct that had happened in Oct but was entered into the system it would appear in the Nov report even though it occurred in Oct. Of course, I don't know if this should have happened in Oct until I see it in the Nov report. I have to go back to the oct report to verify that it wasn't included in the Oct report. I have close to a 1000 items like this on a monthly basis so it takes a while to validate it wasn't in the oct report. Does this make sense. It is trying to determine the timing of the data - in which month it shows up. I would like an easy way to flag the visits when they initially appear in the report.

 

[Ombir]

Check this link for forum guidelines. We have to see your actual data structure first to provide you any kind of help.


http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[ImkeF]

You should join the tables from both months using the Join Kind: Full Outer.
Maybe this articles helps: Merge Tables using Outer Joins in Power Query - The Ken Puls (Excelguru) BlogThe Ken Puls (Excelguru) Blog

Then you create an additional column where you check if there is a value in the current month, then take it and if not, take the value from the previous month:
Pseudocode:
if [ValueFromCurrentMonth]<>"" then [ValueFromCurrentMonth] else [ValueFromPreviousMonth]