Forum Discussion
Show Missing Values in Matrix as Text
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: Row...
I'll have to recreate this and get back to you with it tomorrow. The file I'm working in includes sensitive information.
I will be waiting 🙂
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"