Manager Effectiveness template - can't refresh or identify dynamic data source after publishing

[robert_verner]

I've had this trouble for a while after publishing the Manager Effectiveness template; all the other templates seem to refresh fine, but I can't schedule refresh for this template. I get the below error - I thought perhaps the glossary connection was the problem, but disabling that didn't help. I can't see what Query1 is, so I'm not sure what the problem is. Perhaps it isn't a problem, and the report will still pull in the latest data?

[Dazza]

Hi @robert_verner!

Have you checked your PowerBI file (the published .pbix) to see if there is any query named "Query1" in there? The default template should only have the queries below, so if you see a "Query1" you should be able to delete and re-publish accordingly. To check the queries, open the published .pbix file, click on "Transform data" and review the "Queries" column. If you don't see "Query1" in the list, try refresh and publish the file again to see if that helps. If you still get the message, you may want to redo the template build and publish process...

[robert_verner]

Thank you! I have rebuilt a couple of times. I've tried re-publishing, but nothing has worked yet. I don't make any changes to the Microsoft template other than pasting in the odata query links.  I don't see a query 1, but I do see that this particular template has a different setup than all the other templates. 

let
FileToUse = #"Manager effectiveness query URL",
Source = ImportData(FileToUse),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unscheduled_call_hours_manager_1on1", Currency.Type}, {"Workweek_span", Currency.Type}, {"After_hours_collaboration_hours", Currency.Type}, {"Networking_outside_organization", Int64.Type}, {"Internal_network_size", Int64.Type}, {"Generated_workload_unscheduled_call_hours", Currency.Type}, {"Generated_workload_IM_hours", Currency.Type}, {"Generated_workload_email_hours", Currency.Type}, {"Generated_workload_meeting_hours", Currency.Type}, {"Meeting_hours_with_manager", Currency.Type}, {"Meetings_with_manager_1_on_1", Int64.Type}, {"Meeting_hours_with_manager_1_on_1", Currency.Type}, {"Collaboration_hours_direct_reports_all", Currency.Type}, {"Collaboration_hours", Currency.Type}, {"Instant_Message_hours", Currency.Type}, {"Unscheduled_call_hours", Currency.Type}, {"Email_hours", Currency.Type}, {"Meeting_hours", Currency.Type}}),
#"Add SupervisorIndicator" =
if List.Contains(Table.ColumnNames(#"Changed Type"), "SupervisorIndicator")
then
let
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"SupervisorIndicator", "SupervisorIndicatorSource"}}),
#"Add SupervisorIndicator" = Table.AddColumn(#"Renamed Columns","SupervisorIndicator",each [SupervisorIndicatorSource]),
#"Remove SupervisorIndicatorSource" = Table.RemoveColumns(#"Add SupervisorIndicator", "SupervisorIndicatorSource")
in
#"Remove SupervisorIndicatorSource"
else
if List.Contains(Table.ColumnNames(#"Changed Type"), #"Organizational attribute for supervisor indicator")
then Table.RenameColumns(#"Changed Type",{{#"Organizational attribute for supervisor indicator", "SupervisorIndicator"}})
else Table.AddColumn(#"Changed Type", "SupervisorIndicator", each null, type text),

#"Duplicate SupervisorIndicator" = Table.DuplicateColumn(#"Add SupervisorIndicator", "SupervisorIndicator", "Supervisor Indicator"),

Dimensions =
let
Source = #"Duplicate SupervisorIndicator",
SourceColumnList = Table.ColumnNames(Source),
LastMetricColumnName = "Meeting_hours",
PostionofLastMertic = List.PositionOf(SourceColumnList,LastMetricColumnName),
Dimensions = List.RemoveItems(List.Range(SourceColumnList,PostionofLastMertic+1),{"SupervisorIndicator", "IsActive", "WorkingStartTimeSetInOutlook", "WorkingEndTimeSetInOutlook", "WorkingDaysSetInOutlook"})
in
Dimensions,

#"Duplicate HR attributes" = fnDuplicateColumns(#"Duplicate SupervisorIndicator", Dimensions),
#"Unpivot Dimension Attrbiutes" = Table.Unpivot(#"Duplicate HR attributes", Dimensions, "Dimension", "DimensionValue"),
RenameHeaders = List.Transform(List.Transform(Dimensions, each _ &".Copy"), each {_, Text.Replace(_,".Copy","")}),
#"Rename Filter Attributes" = Table.RenameColumns(#"Unpivot Dimension Attrbiutes", RenameHeaders),
#"Unpivot Filter Attrbiutes" = Table.Unpivot(#"Rename Filter Attributes", Dimensions, "Filter", "FilterValue"),
#"Added Custom" = Table.AddColumn(#"Unpivot Filter Attrbiutes", "PersonIdMonthKey", each Text.From([PersonId]) & "|" & Text.From(Date.Year([Date])) & Text.From(Date.Month([Date])), type text)
in
#"Added Custom"

All the other templates do all those steps in mostly separate queries.

I also notice that the glossary table is populated by a data source - all the other templates have the glossary as a static table: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVZRb9s2EP4rhJ/swfHirF27AHsIhnYLtqTDmqIPcR9o6iQRpkiNpOy4v353JCVLjrLURV4SSzred/fdd8e7v5/cAHipC1aaxrrJfHLbVGuwzOTxDfMlsBqsM5q5GrRnUrMqnnFsJ33JuGcKuPPMaGAG7W06gN6Wky/z+8m7ikv17QgOdEYhcZ0xCwLklp6AnND5i+Dzk3aihKxRkDHB1QnuMYHm6DBZWdMUJbsDXpGXnwLItXae45EKnOMFnIRxdNax6fWNmw2BnifwVYjjN6MUXxvLvUQILyuIJzOJ/HgkqTbWn1q9eaR0nkKJPBjLRMn9c5Et2DCkiCa1UE0GbI2m+ODBaq5CGeEhPbQMvg55DcQXISuukS3LlpdLNhWGixINZoPUqsEpqbdGBYUYrfb9jAkYH6VtnaKXn/u4x5DoAf9Mcwv/NqDFfhT2VMQ3ETFhCHPGvUeBcy3G9DTMbQelBRYPAHQEZ4fCPAP+NoD/DhqwUCT2QdWU4dkY+rEyUBBBGF2b9WGFheA6R4tQc65VL+T1Ho8WXMuvlBad60DaPqeXAbBVH/2v+Kb9dtAnBvvLo+4vnsiO2mSQ3Ysm9V3BL89D9B9Pj32oixegn6JZpiGXK5I7MCcMym0quxe1QT9eckWdcMV0U4GVItl5SlYiCwIDcBjZDvWKZAqjNU4lUmkbpMSQTRogRzY1mFoBzSUKTpiq5nq/YFeslAWZR6wKuHYRsZd7yR1iFIEBewiWsDIrt0D10AV0M1f6VXN+fvHGMQ1+Z+yGSLhIJKQBlb4wJ78iFZl0SJbwh2mWIsciOSLlDqPRXZVSMjRVWrpTRnHS7EpTDRPot/LOhDzxZN6oVMyI1NKjyC5HCSCLsKHJsYwX1W2Keo1cZL58FFiXRxJDUBlm02iZZv2wACNUf2OkIc+nw32V5u/o4SCyccckoQxyqUlWLt5HeQDJsXHMLtC9r1GH+HrQS5cr/QPOxtik4XfbAvGBWpN+oUiAdDi8tRcMiXRwuMWFaVTG1kADwFN7EVsplAOusV3NaCYs2HtsT3jgFcpjTq/3naNLNuWzhN6htM4pT2qxrcyaKOzQRMF5mCxzGjfTdeugq3PnKbcoue41TS2wg4o7XkFvJi5W+gY7zmzB4jWAf/dPVaRqsLol38JQGEMS1o2nz5XBr4Ad7Y++19x6KWTNw3RLIfVQFiSauCxc5dTl37OnjtxnLlyXj/dA03gns1ZceJd+aLwyZnOGToXMJRpSo/U35mXcKT7ja5I5wnPa3O5oS1tjXwLoXnTtBMqldT7Wubs+2iGfxnfWXumxkR6Z0cgHXJBYxjsJhilAYVBgcfW4dn/JDaj9H0ZJspw6FMp7qTwE/jXQHFvhldo41BguNNhOR9cROVxN2K+41LZabPMInR3HBcdBrQIUchOw3I/s77sPyWYavKRjKBtLG7UYWyMdu2DUhhm3GXb9VvI4W/gOww+CDnhDQkmlY+5mC/ZJY5XFhtoHHsIGhSpfTf6BolHc/l92tmeCGVzh1RV/EveI1+vNSiKBI4V+nlm3mlAJlm8H5fpTmx3qsgASFir9ibJ1ZqH0+C1k8g4HjdnTAhD3eN0Rjjp5HURE12rku4aY4KFxhjFOj/rIDRspLVFuRpzeGn22OT0kheOKiqoxuJcNarT6xDYukl/+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Metric = _t, Definition = _t, #"Metric number" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Metric", type text}, {"Definition", type text}, {"Metric number", Int64.Type}})
in
#"Changed Type"

[robert_verner]

The problem isn't the glossary table query

[Dazza]

Interesting - I'm going to download a new template to see what I experience - will feedback once I've tested

[Dazza]

I get the same behaviour! I'm also unable to locate which data source it is referring too... think someone from the product team will need to feedback on this one 🙂

[robert_verner]

Thank you so much for checking!

I love seeing how they build their templates - I have learned a lot from them

[Florine_Segers · Accepted solution]

Hi @robert_verner  and @Dazza, thank you for flagging the issue. 

We looking into this and noticed this report allows you to use either an OData link or a link to a .csv file as a data source. That is what seems to be causing the issue. We will take the action to limit the data source options to OData link only and let you know when the updated version of the PBI template is available in the product (expect it will take about 3 weeks). Our apologies for the inconvenience! 

[robert_verner]

Excellent. I tried changing the source line to this for both queries (just like the other templates), and that seemed to do the trick:

Source = OData.Feed(FileToUse, null, [Implementation="2.0", Concurrent = 1]),

[Florine_Segers]

Glad to hear it worked Robert, and that you are good to go for now. Will circle back when we updated the template in product as well.