Facility Management and Location Readiness Dashboard Hotfix
(12-14-2020)
Download the Power BI Pbit files from GitHub: https://github.com/microsoft/powerapps-tools/tree/master/Apps/ReturnToWorkplace
These are the steps to fix the data-source schema changes for RTW Release 1.3
1) Open Power BI Desktop and open the PBIX for Facility Manager / Leadership
2) Transform Data and select Transform Data
3) In COVID Pubic data group of queries, change the query below:
- Reproductive number - Open advanced editor and copy the code below
let
Source = Csv.Document(Web.Contents("https://raw.githubusercontent.com/epiforecasts/covid-rt-estimates/master/national/cases/summary/rt.csv"), [Delimiter= ",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"country", type text}, {"date", type date}, {"median", type number}, {"mean", type number}, {"lower_90", type number}, {"upper_90", type number},{"upper_50", type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","United States of America","United States",Replacer.ReplaceText,{"country"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([country] <> "United States")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "region", each null),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"country", "region", "date", "type", "median", "lower_90", "upper_90", "lower_50", "upper_50", "lower_20", "upper_20"}),
#"Appended Query" = Table.Combine({#"Reordered Columns", R_States}),
#"Merged Countries" = Table.NestedJoin(#"Appended Query", {"country"}, Country, {"Country"}, "Countries", JoinKind.LeftOuter),
#"Expanded Countries" = Table.ExpandTableColumn(#"Merged Countries", "Countries", {"Country Code"}, {"Country Code"}),
#"Merged States" = Table.NestedJoin(#"Expanded Countries", {"state"}, #"Province/State", {"Province/State"}, "States", JoinKind.LeftOuter),
#"Expanded States" = Table.ExpandTableColumn(#"Merged States", "States", {"Province/State code"}, {"Province/State code"}),
#"Added Custom1" = Table.AddColumn(#"Expanded States", "Location ID", each if [#"Province/State code"]=null then [Country Code] else [Country Code] & [#"Province/State code"]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"country", "Country"}, {"region", "Region"}, {"median", "Median"}, {"lower_90", "Lower 90"}, {"upper_90", "Upper 90"}, {"lower_50", "Lower 50"}, {"upper_50", "Upper 50"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Location ID] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Country", "Region", "strat", "date", "type", "mean", "sd", "Median", "Lower 90", "Upper 90", "Lower 50", "Upper 50", "lower_20", "upper_20", "state", "Country Code", "Province/State code", "Location ID"})
in
#"Removed Other Columns"
4) In COVID non loaded tables, change the query below:
- Bing-ChinaOnly
let
Source = Csv.Document(Web.Contents("https://media.githubusercontent.com/media/microsoft/Bing-COVID-19-Data/master/data/Bing-COVID19-Data.csv"),[Delimiter= ",", Columns=15, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Updated", type date}, {"Confirmed", Int64.Type}, {"ConfirmedChange", Int64.Type}, {"Deaths", Int64.Type}, {"DeathsChange", Int64.Type}, {"Recovered", Int64.Type}, {"RecoveredChange", Int64.Type}, {"Latitude", type number}, {"Longitude", type number}, {"ISO2", type text}, {"ISO3", type text}, {"Country_Region", type text}, {"AdminRegion1", type text}, {"AdminRegion2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ISO2] = "CN" or [ISO2] = "TW" or [ISO2] = "HK" or [ISO2] = "MO") and [AdminRegion1] = ""),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Updated", "Date"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Date", "Confirmed", "ConfirmedChange", "Deaths", "DeathsChange", "Latitude", "Longitude", "ISO2", "Country_Region"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns",{{"ISO2", "LocID"}})
in
#"Renamed Columns1"
5) Open Data Source Setting under File -> Options and Settings -> Data source settings -> 'Edit Permissions'
for https://media.githubusercontent.com/media/microsoft/Bing-COVID-19-Data/master/data/Bing-COVID19-Data... to Credentials: Anonymous and Privacy Level: Public.
Please let us know if you have any questions
abe
