Hi Aye,

In Power Query you can bring the 'Available Bed' column into the Occupancy table by merging them and using the Facility and date columns for the match. Then you can add a column that divides one column by the other. You'll need to remove the time portion of the date from the AccommDate column so it can match the Month Start column.

Mynda

Hi Mynda,

Many thanks for your prompt reply.

My data are from different sources. SB_SCC_Occupancy is from our main accounting software, imported from ODBC as you advised earlier and Stats-Act-FY2019 is from access. I now imported both files to power BI. Then trying to clean them through edit query from power BI.

what would be more efficient way to get them clean live and present it on power BI. It is a daily report and I will have to report daily. 

If I can still do the merging from different sources, can you please guide me the chapter I can refer from power BI or power query. I am a member of the entire bundle. I have to refresh myself to get this work. 🙂

Regards,

Aye

Hi Mynda,

Sorry for being a pain.

I now place my files on power query.

Please see attached.

I have to fix my dates from FY period in Stats-Act-FY2019 as per SB_SCC_Occupancy.

SB_SCC_Occupancy are daily figures generated by the system for each facility.

Please guide me with diagram if possible to get the daily occupancy from Occupants/Available Bed as per date and facility.

Thank you so much.

Regards,

Aye

Hi Aye,

It appears from the images in your first Word document that you just need to set the data type for the AccommDate field to Date only, not date and time, as it is now. Then you can merge the tables, as explained in session 3.04 of the Power Query course, to bring in the 'Available Bed' column from the Stats-Act table. Once you have both columns in the one table you can divide one by the other with a custom column, as per session 4.14.

In the images in the second Word document your Stats-Act table is now missing the Month Start column. If you have the Month Start column available in the source then I'm not sure why it's not in the query table.

Mynda

Hi Mynda,

Can I have days of the month column instead of month start column.

Can you please let me know where can I find the formula for days to be applied to each period for each facility.
I think it will be ok if I can merge two table with facility and date to be the same.

I will try this step first then I will do merging. Please see attached for what I mean.

Regards,
Aye

Hi Aye,

No, you need a proper date to match to the other table. You don't have 'days of the month' in the other table, so it can't do a match.

I don't know what this means "Can you please let me know where can I find the formula for days to be applied to each period for each facility." What 'days' and what is being 'applied to'?

Mynda

Hi Mynda,

Sorry for confusion, I did not mean days, I mean dates.

My FY Period from Stats_Act_FY2019 should be dates to match with AccomDate from SB_SCC_Occupancy.

If I do "start date of the month", it will not be match and I cannot have daily occupancy. 

It is a daily report and I will have to have daily dates in both tables.

I now attached mock excel report and please have a look result sheet for what I wanted to have it as a result for Stats_Act_FY2019.

Thank you so much.

Regards,

Aye

Hi Aye,

Thanks for attaching the Excel file. Please do this with every question where possible as this is the clearest way to explain what you're trying to do.

You'll find the attached file contains the solution. It's not as simple as expanding the rows by date because you're using fiscal periods.

Mynda

Thanks, Mynda,

That is just a sample result.

I have 60 other facilities to be done in that way and it is rolling.

I just want to know how can I do that (the steps to be followed) in power query.

My original file is from Access.

Regards,

Aye

Hi Mynda,

If expanding the rows by date as per fiscal period is not too simple, can I try a different one.

I tried to add a column for fiscal period in Daily occupancy table.

And then I just want to merge those two tables in power query via power BI. However when I hit edit query from Power BI, I have that fiscal period column disappeared in power query.

Please let me know how to fix that.

Thank you.

Regards,

Aye

Hi Aye,

The process is the same irrespective of how many facilities you have or whether the data comes from Access or the sample in the Excel file you provided. You just need to create a blank query and copy the steps in my file to create the date table, then you can merge it with your Access source data query. Try it in Power Query in Power BI and you'll see.

Mynda

Thank you so much, Mynda.

I got this work now, Finally. 

Much appreciated for being so patient all the way.

Regards,

Aye