Show Missing Values in Matrix as Text

[Steven1809]

Hello,
I'm creating a dashboard for monitoring whether documents are up to date in our database (web based, linked via API) and can't figure out a way to show missing data as missing.

Data:

Rows that include document name, parent, as of date, and a document tag (ex: "ADV"). There can be multiple versions of the same document so I just use the one with the most recent as of date. There are also many rows in this data that aren't relevant for monitoring.

To conditionally format the data I use a column in power query to take the amount of days since the document as of date and assign good/warning/late depending on the type of document. In combination with this I have a simple pie chart that shows the % in good/warning/late.

Some documents may not be in the database, or not tagged appropriately and leading to blank rows in the matrix and an inaccurate representation in the pie chart. 

Is there a way to assign a "missing" value to these rows if no document with the appropriate tag appears? 

[HarishKM]

@Steven1809 Hello ,
You can update conditional column as you have 3 values already like Good, Late and Warning so you can add not available when you do not have values or blanks or you desired output.
Kindly refer below image.

Thanks
Harish M

[Steven1809]

The issue with this method is it wouldn't differentiate by which parentID and document type. I create the good/late/warning based on a document type and days since the as of date. I need a way to basically check that the document has never appeared in the data feed for each unique parentID.

[AmiraBedh]

Can you provide your input ? There are several ways for that 🙂

[Steven1809]

Do you mean the file itself? Or what a row in power query looks like?

If you mean power query, the format is at follows:

parentID/documentType/Document Versions.asOf/DateDiff (today-as of date)/OverDue (assigned depending on document type and date diff)

Matrix:

I use first OverDue so that I only show the most recent document version

[AmiraBedh]

The file itself please, help us to help you.

[Steven1809]

I'll have to recreate this and get back to you with it tomorrow. The file I'm working in includes sensitive information. 

[AmiraBedh]

I will be waiting 🙂

[Steven1809]

Sorry for the delay. I don't have an option to attach files in this forum for some reason so here is some additional information

excel input:

parentID	documentTypes	Document Versions.asOf
Parent1	Conference Call	9/1/2023
Parent2	Update:quarterly	6/29/2023
Parent3	Update:quarterly	3/30/2023
Parent4	Attribution	9/29/2022
Parent2	Conference Call	8/31/2023
Parent1	Attribution	5/30/2023
Parent3	Notes	3/13/2023
Parent4	ADV	12/31/2022
Parent1	Factsheet	12/31/2022
Parent2	DDQ	12/31/2022
Parent3	Audited Financials	12/30/2022
Parent4	ADV	8/31/2022
Parent2	Notes	8/30/2023
Parent1	Notes	8/11/2023
Parent3	Update:quarterly	6/29/2023
Parent4	DDQ	4/29/2023
Parent1	Update:quarterly	3/30/2023
Parent2	Notes	8/30/2023
Parent3	DDQ	8/30/2023
Parent4	Update:quarterly	7/31/2023
Parent2	Factsheet	6/29/2023
Parent1	DDQ	8/29/2023
Parent3	Notes	8/24/2023
Parent4	DDQ	6/29/2023
Parent1	ADV	6/29/2023

power query: 

let
Source = Excel.Workbook(File.Contents("Document Tracking Sample.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"parentID", type text}, {"documentTypes", type text}, {"Document Versions.asOf", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Duration.Days(Date.From(DateTime.LocalNow())-Date.From([Document Versions.asOf]))),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "DateDiff"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom.1", each if [documentTypes] = null then null
else if [documentTypes] = "ADV" and [DateDiff] < 400 then "Good"
else if [documentTypes] = "ADV" and [DateDiff] < 500 then "Warning"
else if [documentTypes] = "ADV" and [DateDiff] >= 500 then "Late"
else if [documentTypes] = "Attribution" and [DateDiff] < 120 then "Good"
else if [documentTypes] = "Attribution" and [DateDiff] < 150 then "Warning"
else if [documentTypes] = "Attribution" and [DateDiff] >= 150 then "Late"
else if [documentTypes] = "DDQ" and [DateDiff] < 400 then "Good"
else if [documentTypes] = "DDQ" and [DateDiff] < 500 then "Warning"
else if [documentTypes] = "DDQ" and [DateDiff] >= 500 then "Late"
else if [documentTypes] = "Update:quarterly" and [DateDiff] < 120 then "Good"
else if [documentTypes] = "Update:quarterly" and [DateDiff] < 150 then "Warning"
else if [documentTypes] = "Update:quarterly" and [DateDiff] >= 150 then "Late"
else if [documentTypes] = "Factsheet" and [DateDiff] < 120 then "Good"
else if [documentTypes] = "Factsheet" and [DateDiff] < 200 and [DateDiff] >=120 then "Warning"
else if [documentTypes] = "Factsheet" and [DateDiff] >= 200 then "Late"
else if [documentTypes] = "Notes" and [DateDiff] < 180 then "Good"
else if [documentTypes] = "Notes" and [DateDiff] <= 250 then "Warning"
else if [documentTypes] = "Notes" and [DateDiff] > 250 then "Late"
else null),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Custom.1", "OverDue"}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Renamed Columns1",{{"Document Versions.asOf", Order.Descending}}))
in
#"Sorted Rows"

---