Method to deal with missing current data in a table and replacing it with previous month's data

[Anonymous]

Hi Everyone.

So I have a issue where I have a data set containing number of hours worked for a list of sites and we have another table with the previous month's hours worked at site level. 

I also have a master list of just all available sites residing in another table without any hours worked data.

So three separate tables in total.

What I need to do is where the current month's hours worked for a particular site isn't available, I need to replace any null values with the hours worked data contained in the previous month's hours worked data table for the same corresponding site.

Then as a "table" visualisation I just need to show what the missing sites were for the current month (that were showing originally as null) and showing the tranposed amount of hours worked data obtained from previous month's hours for each of those particular sites.

Has anyone got any thoughts on how one might achieve this?

[Anonymous]

Thanks for responding. Sorry it has taken a while to reply.

So the master list of sites as a source file is csv and below is example of the columns with dummy data to show content formatting

Division	Sector	Combined Div/Sec (with dashes)	Combined Div/Sec
Test	Test QLD	Test - TestQLD	TestTestQLD

The data from the sites resides in a directory folder containing xlsx files from each site. When ingested into PowerBI (DivSec Date Folder) the data table structure looks like this:

Source.Name	Division - Sector	Site/Address	Workers	Hours	Time Period (Description)	Period Date
test.xlsx	Test - Test1	51 Test Place, Bunyip Qld 4000	45	12279	2023 (05) May	1/05/2023

Then in PowerBI we merge query both these tables together ("Missing Business Data" Query)  to get an idea of what sites haven't submitted data for the current month as they will have "null" values. After actioning the relevant applied steps in the query we get the following output in the query

Employee Type	Division	Sector	DIV-SEC
Contractor	Test	Test QLD	TestQLD

Then we have the previous month's data imported as a CSV file and applied steps added to a query called "DIVSEC Prev Month". The output is shown below

Division	Sector	Employee Type	Time Period	Workers	Hours	DIV-SEC
Test	Test QLD	Contractor	2023 (03) March	3	40	TestTest QLD

Then this is where I hit a problem. So I then went to create a separate query called "DIVSEC Missing" merging both the "Missing Business Data" Query & the "DIVSEC Prev Month"  query together to try and get the sites showing the "Missing Business Data" Query to populate with the hours for the same site showing in the "DIVSEC Prev Month" query. Currently I'm not see the same number of "missing sites" (vs missing site query output) in the output (there is a larger number of sites visible) when I create a "table" on the front digital report canvas pulling from the "DIVSEC Missing" query.

I hope that makes some sense to you. 

Kind Regards,
David.

[314mp_M0th4]

Could you provide an image of your table? Is it the hours worked for each employee or a team? What is your data source? Is it an excel or csv file uploaded monthly? If so this is simple. Please providde me with more info on the data source.

[Anonymous]

Thanks for coming back to me. Confirming it is number of employees per each Division/Sector combo.

Attached are the screenshots of the table schemas for each table I mention in my earlier post.

Master list of sites table:

DivSec Data Folder Table:

DIVSEC Prev Month Table:

I hope this is what you are looking for?

[314mp_M0th4]

In the current month table you could create a calculated column with the statement :

IF(Isblank(current_month_hours), previous_month_hours, current_month_hours))

and another one that shows which columns were null:

If(Isblank(current_month_hours), True, False))

Now you can conditionally format your table with red and green for missing and existing data. Of course, remember to insert your column names in the calculation. Hope this works.

[Anonymous · Accepted solution]

Thank you so much - your reply whilst in theory would have worked - it didn't in my particular case - but I could use this for further scenarios and it prompted me to reflect on what I had already built and to review it again once more. See below.

What I ended up doing is going back through all three queries and I discovered that a few columns needed to renamed so they match in other queries and I also forgot to extend a table column in a merged query in a separate "missing data" query I built which I then filtered out the current month data so it pulled through the correct previous DIVSEC Prev Month Table that had previous month's hours data assigned to current month's missing DIVSEC and then this was merged with current month DivSec Data Folder Table by building a separate "Combined DIVSEC incl March Missing" query I had originally built. In this "Combined DIVSEC incl March Missing" query I have a source name which told me whether the source was from the previous month (march) or the current month.

Then in the front end I built a matrix visual and remove the current month data so only the previous month's hours assigned to the current date period were showing. Feeling very accomplished - it just took me time to review again what I had done and double check my work. I was around 80% there originally - but it was just the little things that prevented me seeing the solution. The joys of dealing with data! 🙂

[314mp_M0th4]

Nice work!

[Anonymous]

Thank you 🙂