Could you please consider modifying the "Get Recommendations" query so that the ".properties.extendedproperties....." fields are accessible to report on?
I'd like to be able to include the potential cost savings (.properties.extendedProperties.savingsAmount) on a report (even just aggregating at a subscriptions level would be fine).

Id' like to surface this data via a Sharepoint web part for consumption across our organisation. Would it be possible to automate a (daily?) refresh of the data? Regenerating the recommendations is automate-able but I can't see how the other queries could be automated.

Describe the bug
Unable to open CCO Azure Infrastructure Dashboard template. There is some Unrecognized JSON Property error.

To Reproduce
Steps to reproduce the behavior:

1. Go to PowerbI desktop. Prior have Account logged on and clear previous credentials,
2. Click on File--> Import Template --> Import 'CCO Power BI Infra Dashboard v8.0.pbit'
3. Wait for couple of second, JSON error notification will pop up.
4. See error, C:\Users\Whicheveruser\Microsoft\Power BI Desktop Store App\FrownSnapShot1253893658.zip

Expected behavior
Template is not importing and functioning despite user logged in PowerBi is having sufficient permission as well as Resource Provider enabled

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: Windows 10 Pro
• Browser Not Applicable
• Client PowerBI Desktop Client
• Version 19.09
• Additional details attached
  CCOS Dashboard Frown.txt

Smartphone (please complete the following information):
Not Applicable

Additional context
Add any other context about the problem here.

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Hi there,

All is in the title, every dashboard works fine, but RBAC dashboard is fully empty.

Any idea ?
Thanks

Describe the bug
When performing a refresh for all data, there seems to always be a continuous prompt to login to the Graph API which becomes a pain when you are prompted about 3+ times. What I have done is login once, cancel every other login prompts (which in turn messes up the refresh) then do a new refresh which gets all the data.

To Reproduce
Steps to reproduce the behavior:

1. Performing a refresh within a customers tenant (non Microsoft internal tenant)
2. Click on the refresh icon
3. Wait for the refresh for Service Principal data
4. After the initial prompt to login to the graph API, you will notice multiple prompt attempts.

Expected behavior
Similar to logging in to the management.azure.com API, logging in using the Graph API should only be a one time prompt.

Screenshots
N/A (I can do a remote session)

Desktop (please complete the following information):

• Power BI Desktop (October release)

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
Data source for "Network Interfaces"
Data source for "Resource Groups"
Data source for Resources
Data source for "Public IPs"
Data source for "Virtual Machines"
Data source for "Get Recommendations"
Data source for "Get Security Center Alerts"
Data source for "Azure Regions"
Data source for "RBAC Role Definitions"
Data source for "RBAC Role Users"
Data source for TaggedResourceGroups
Data source for TaggedResources
Data source for "RBAC Role groups"
Data source for "RBAC Role Users and Groups"
Data source for "Service Principals"
Data source for NSGs
Data source for "Security Tasks"
Data source for "Security Tasks Final"
Data source for "VNET Peerings"
Data source for "Virtual Networks"
Data source for Subnets
Data source for Disks
Data source for ComputeResourcesUsage
Data source for NetworkResourcesUsage
Data source for StorageResourcesUsage
Data source for "Security Recommendations"
Discover Data Sources
Query contains unsupported function. Function name: Web.Contents

We cant update because use dynamic data sources. https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-sources

regards

Hi CCO Dashboard Team,

first of all I want to say that this Dashboard looks absolutely brilliant for me. Thank you so much for your work!

Unfortunately I have a problem to get the Power BI Template up and running.
I tested this on different Clients and received the same error every time.

The requirements you mentioned (like Win10 above 14393.0, Internet access & permissions) are met.

Description the bug/issue
When I open the template (indenpendent which one actually), I am asked to enter tenantName. After I have to connect which a user with the appropiate permissions (also tried different users with/without MFA) in our Azure Environment. After this it searches for all the different resources within my environment, but fails after some time with this error:

Network Interfaces
The column 'Column1' of the table wasn't found.
Resource Groups
The column 'Column1' of the table wasn't found.
Resources
The column 'Column1' of the table wasn't found.
Public IPs
The column 'Column1' of the table wasn't found.
Virtual Machines
The column 'Column1' of the table wasn't found.
Get Recommendations
The column 'Column1' of the table wasn't found.
Get Security Center Alerts
The column 'Column1' of the table wasn't found.
Azure Regions
The column 'Column1' of the table wasn't found.
LastRefresh_Local
Loading blocked by failures with other queries.
RBAC Role Definitions
The column 'Column1' of the table wasn't found.
RBAC Role Users
The column 'Column1' of the table wasn't found.
TaggedResourceGroups
The column 'Column1' of the table wasn't found.
TaggedResources
The column 'Column1' of the table wasn't found.
RBAC Role groups
The column 'Column1' of the table wasn't found.
RBAC Role Users and Groups
The column 'Column1' of the table wasn't found.
Service Principals
The column 'Column1' of the table wasn't found.
NSGs
The column 'Column1' of the table wasn't found.
VNET Peerings
The column 'Column1' of the table wasn't found.
Virtual Networks
The column 'Column1' of the table wasn't found.
Subnets
The column 'Column1' of the table wasn't found.
Disks
The column 'Column1' of the table wasn't found.
ComputeResourcesUsage
The column 'Column1' of the table wasn't found.
NetworkResourcesUsage
The column 'Column1' of the table wasn't found.
StorageResourcesUsage
The column 'Column1' of the table wasn't found.
All Tenants
Loading blocked by failures with other queries.
Tenant domains
Loading blocked by failures with other queries.
TenantManagement
The column 'Column1' of the table wasn't found.
All Subscriptions
The column 'Column1' of the table wasn't found.

After opening the Query Editor, I can see that this causes by "All Subscriptions" query:

I actually don't know what I'm doing wrong here, but it does not work with no of my clients, different users etc.
Maybe you know about this problem and can give me a hint what's the problem here?

Thank you so much!
Kind regards, Daniel

Hi,
I am new to power BI.. there are some problems when I try to connect to this CCO Dashboard...

Describe the bug
while I try to connect the CCO Dashboard, I downloaded the 'CcoDashboardAzureConnector.mez' in the path 'C:\Users...\Documents\Power BI Desktop\Custom Connectors'.. But when I open Power BI Desktop, there is no action about this connector, and I could't connect to the data source

To Reproduce
Steps to reproduce the behavior:

1. download CcoDashboardAzureConnector.mez and save it to 'C:\Users...\Documents\Power BI Desktop\Custom Connectors'
2. set Security option to (Not Recommended) Allow any extension to load without validation or warning
3. reopen power BI Desktop
4. clear all data source permission
5. there is no 'refresh' button for me to click. and the dashboard is blank.
6. And the click 'Get Data' -- 'Web'
7. I use 'https://management.azure.com/subscriptions' as the URL to connect
   8.click 'Organizational account' -- 'sign in', it shows 'credential type is not supported'

Screenshots

Describe the bug
A clear and concise description of what the bug is.

To Reproduce
Steps to reproduce the behavior:

1. Go to '~\ccodashboard\dashboards\CCODashboard'
2. Click on 'CCO Power BI Dashboard v6.3'
3. Input TenantName
4. SignIn to Organizational account with Global Admin privileges for https://management.azure.com/
5. Connect Anonymous access for https://github.com
6. See error

Expected behavior
Expect to no issue and move on to providing credentials for graph.windows.net

Screenshots
If applicable, add screenshots to help explain your problem.

Describe the bug
A clear and concise description of what the bug is.

To Reproduce
Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Hi,
I have a forbidden access, any idea ?

Could you please update the powershell scripts to use AZ, rather than AzureRM?

I am trying to use the dashboard to show all my subscriptions in the EA portal, I am the service administrator in all of these subscriotions, but when I Introduce my org account, it only shows 1 subscription which it's associated to the EA and another subscription associated to the MSDN. How can we list all the other subs from the EA portal? Or even If I want to connect the CCO to a free tenant which I recently created? Can you suggest any idea?

We would like to get Card browser Custom visual certified as we are unable to use the Custom Visual as our Corp Compliance Policy doesn't allow use of uncertified visuals.

This is an issue with the governance dashboard.

Have followed all the instructions (pretty sure i have) and i can see a number of rows being loaded in but then it pops up on data load with "oops something went wrong" then then fails with the following:

Column 'Assesstment name' in Table 'SecureScoreControlDefinitions_F' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

Any ideas?

Feedback Type:
Frown (Error)

Error Message:
Failed to save modifications to the server. Error returned: 'Expression in partition 'n/a' in table 'n/a' references an unknown entity.
'.

Stack Trace:
at Microsoft.PowerBI.Client.Windows.Storage.FileOpenError.HandleError(IWindowService windowService, LocalizedString title)
at Microsoft.PowerBI.Client.Windows.Services.TemplateManager.TryImportTemplate(IPowerBIWindowService windowService, IQueryServices queryServices, IExceptionHandler exceptionHandler, TelemetryUserActionId entryPoint, Boolean& editQueries, String filePath)
at Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.DataImportCommands.ImportTemplate(IPowerBIWindowService windowService, IExceptionHandler exceptionHandler, ImportTemplateParameters parameters)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Failed to save modifications to the server. Error returned: 'Expression in partition 'n/a' in table 'n/a' references an unknown entity.
'.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.Windows.Utilities.UIBlockingAwareExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<>c__DisplayClass4_0.<.ctor>b__0(Object arg)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.d__10.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.Execute(Object parameter)
at Microsoft.Practices.Prism.Commands.DelegateCommand1.<Execute>d__5.MoveNext() at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine) at Microsoft.Practices.Prism.Commands.DelegateCommand1.Execute(T parameter)
at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(ICommand command, Object parameter) at Microsoft.Practices.Prism.Commands.CompositeCommand.Execute(Object parameter) at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(T parameter)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass2_6.<

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.81.5831.1181","ModuleName":"Microsoft.PowerBI.Client.Windows.dll","Component":"Microsoft.PowerBI.Client.Windows.Storage.FileOpenError","Error":"Microsoft.AnalysisServices.OperationException","MethodDef":"HandleError - PFE_M_ENGINE_UNKNOWN_ENTITY_REFERENCED","ErrorOffset":"76"}

Snapshot Trace Logs:
C:\Users\username\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1992385161.zip

Model Default Mode:
Empty

Model Version:
PowerBI_V1

Is Report V3 Models Enabled:
True

Performance Trace Logs:
C:\Users\username\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_NewWebTableInference
PBI_qnaLiveConnect
PBI_realTimeRefresh
PBI_showMinervaRibbon
PBI_showMinervaViewNavigator
PBI_v3ModelsPreview
PBI_hierarchicalSlicerCreation

Disabled Preview Features:
PBI_inlineExplore
PBI_relativeTimeSlicer
PBI_dataSearchCuration

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBINonFatalError_MethodDefDescription:
PFE_M_ENGINE_UNKNOWN_ENTITY_REFERENCED

Formulas:

section Section1;

When opening CCO Dashboard, all queries display the following error:

When looking into the query, the Source is fine on all queries, but all the next steps (Invoke Custom Function) fail with the above error.

Privacy setting is set to None on all connections. Private and Organization has also been tested but the result is the same.

Rebuild has been tried as described here: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ to no avail

Not sure if this is part of the solution: https://social.technet.microsoft.com/Forums/en-US/ca434e2d-88fe-4962-b46a-a1db51e8bd89/feedback-wanted-behind-the-scenes-of-the-data-privacy-firewall?forum=powerquery

When running functions (like ListResourceGroups) on specific subscriptionId in a manual query, the resource groups are listed as expected.

Expected behavior
Description of how to mitigate Formula.Firewall on CCO Dashboard.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: Windows 10
• Version 19041.508

Additional context
Power BI version: 2.85.985.0 64-bit (September 2020)

I am familiar with Azure but new to Power BI. I think it'd be very helpful to provide detailed steps on how to install/import the dashboard into Power BI dashboard.

Here is what I did:

1. install Power BI Desktop
2. check out the git repo
3. import the Dashboard template (the *.pbit file) into Power BI

It seems to be working for me but it is not clear to me what to do with all the other files in the repo. For example, what to do with the "images" folder, etc.

Describe the bug
At Refresh stage " error - ASC: VMs and Apps recommendations
The remote server return an HTTP status code '404' when trying to access 'https://docs.microsoft.com/en-us/azure/security-center/recommendations-compute-and-apps#compute-and-app-recommendations-'."

To Reproduce
Steps to reproduce the behavior:
Refresh dataset, then due to ASC Vms issue, all other tables are stopping from refreshing.

Expected behavior
Get latest data.

Screenshots

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: N.A
• OS: Win 10
• Browser N.A
• Version N.A

My login has access to many tenants & subscriptions. It would be very helpful to be able to pick a tenant and subscriptions so I do not generate the report for tenants I do not need to review.

If there is a way to do this now, could you tell me how to go about this?

Thanks!

I'm guessing the ASC recommendations output format has changed because I am now getting this error, whereas a few weeks ago it was working fine.

Even though there is a link to the installation guidance, the page seems to be missing. At least the link does not work and I cannot find an md file in the install folder.

Wondering whether it is possible to install the dashboard on PowerBI SaaS?

first of thanks for all the hard work. I was trying to look at the deployment guide and the below link seems to be broken

https://github.com/Azure/ccodashboard/blob/master/install/DeploymentGuide.md

Please list in CCODashboard

1. App service plans
2. App services
3. Event hubs
4. Stream analytics jobs
5. Data bricks jobs
6. Azure functions
7. Service Bus
8. Cost Advisor

I have a request from a large customer to show CPU and RAM data for VMs. Don't know if this would be a overkill. Told them Azure Advisor already tells them when a resource is being underutilized.

When opening the pbit file and logging into a CSP tenancy, I'm running into a load problem, probably because there are no management groups and there isn't a table called tenants in the Resource Groups

If I run the CcoDashboardDConnector without the pbit, then I'm able to pull down the all the tables available. The get entities Management Group shows this:

Kind Regards,

Rob

We're trying to run the CCO Report.
Every time we run the data collection, it ends in the following error:

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E

We tried all the issue fixes and went to all fixed in the Troubleshooting Guide but nothing helped.

On the V5.2 we encountered the below error. To bypass it we spitted the VNET Peerings query to tow, a staging and transformation one.

VNET Peerings
OLE DB or ODBC error: Query 'Unique VNET Peerings' (step 'ListVNETPeerings') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Would it be possible to enhance the portal such that this kind of dashboard could be included in the portal itself?

Describe the bug
When trying to open the template, after making the connection, Power BI throws Object reference not set to an instance of an object. I have tried this on different workstations in different environments connecting to different tenants with different users. All produce the same error.

To Reproduce
Steps to reproduce the behavior:

1. Open PBI
2. Clear credentials
3. Import CCO Power BI Governance Dashboard v2.1 template
4. Select global
5. Add login information
6. On data refresh, PBI throws the error.

Error Message:
Object reference not set to an instance of an object.

Stack Trace:
at Microsoft.Mashup.Evaluator.ChannelMessenger.CreateChannel()
at Microsoft.PowerBI.Client.OleDbProvider.PowerBIMashupDataSource.EvaluationSession.Dispose()
at Microsoft.Mashup.Host.ProviderShared.PackageMashupEvaluator.<>c__DisplayClass7_0.b__1(Object o)
at Microsoft.Mashup.Evaluator.Interface.InvokeManyAction1.CheckDone() at Microsoft.Mashup.OleDbProvider.DataHost.MashupEvaluator.<>c__DisplayClass4_1.<Evaluate>b__1() at Microsoft.Mashup.OleDbProvider.DataHost.AsyncResultIDataReaderSourceHelper.InvokeThenOnDispose(Action1 callback, AsyncResult1 result, Action action) at Microsoft.Mashup.OleDbProvider.DataHost.MashupEvaluator.<>c__DisplayClass4_0.<Evaluate>b__0(EvaluationResult21 result)
at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose(Action1 callback, EvaluationResult21 result, Action action)
at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose[T](Action1 callback, EvaluationResult21 result, Action action)
at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation1.Complete(EvaluationResult21 result)
at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation`1.Cancel()
at Microsoft.Mashup.Host.ProviderShared.NotifyingMashupEvaluator.MashupEvaluation.Cancel()
at Microsoft.Mashup.OleDbProvider.MashupRowset.AbortIfNotComplete(Boolean timedOut)
at Microsoft.Mashup.OleDbProvider.MashupRowset.Microsoft.OleDb.IDBAsynchStatus.Abort(HCHAPTER hChapter, DBASYNCHOP eOperation)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IDBAsynchStatus.Abort(HCHAPTER hChapter, DBASYNCHOP eOperation)

Stack Trace Message:
Object reference not set to an instance of an object.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass15_0.b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.b__0(Object null)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func1 showModalFunction) at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IQueryServices queryServices, IModelingService modelingService, IUIHost uiHost, LocalizedString title, LoadToModelContext loadToModelContext, IEventAggregationService eventAggregationService) at Microsoft.PowerBI.Client.Windows.Services.TemplateManager.PromptForParametersAndCredentials(IPowerBIWindowService windowService, IQueryServices queryServices, IExceptionHandler exceptionHandler, String filePath, Report templateReport, Boolean allCredentialsSatisfied, Boolean& editQueries) at Microsoft.PowerBI.Client.Windows.Services.TemplateManager.TryImportTemplate(IPowerBIWindowService windowService, IQueryServices queryServices, IExceptionHandler exceptionHandler, TelemetryUserActionId entryPoint, Boolean& editQueries, String filePath) at Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.DataImportCommands.ImportTemplate(IPowerBIWindowService windowService, IExceptionHandler exceptionHandler, ImportTemplateParameters parameters) at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action) at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<>c__DisplayClass4_0.<.ctor>b__0(Object arg) at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<Execute>d__10.MoveNext() at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine) at Microsoft.Practices.Prism.Commands.DelegateCommandBase.Execute(Object parameter) at Microsoft.Practices.Prism.Commands.DelegateCommand1.d__5.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.Practices.Prism.Commands.DelegateCommand1.Execute(T parameter) at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(ICommand command, Object parameter)
at Microsoft.Practices.Prism.Commands.CompositeCommand.Execute(Object parameter)
at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(T parameter) at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action) at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Delegate.DynamicInvokeImpl(Object[] args) at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme) at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme) at System.Windows.Forms.Control.InvokeMarshaledCallbacks() at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Form.ShowDialog(IWin32Window owner) at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass1_0.

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.85.284.0","ModuleName":"","Component":"Microsoft.Mashup.Evaluator.ChannelMessenger","Error":"Microsoft.Mashup.Host.Document.SerializedException - System.NullReferenceException","MethodDef":"CreateChannel","ErrorOffset":""}

I am unable to load/use the new v2 CCO Governance dashboard. It is failing on :
= Table.RemoveColumns(blueprints,{"id", "tenantId", "countryCode", "displayName", "domains", "tenantCategory", "ManagementGroups.count"})
Expression.Error: The column 'id' of the table wasn't found.
Details:
id

It looks like the data being returned from Source into the various tables is not in line with what is expected. Anyone else having this issue? v8 of the inf dashboard is working well thx.

Simple question I know but I am trying to connect to my Azure subscription (MSDN if that makes a difference). But getting "We couldn't authenticate with the credentials provided".
The Tenant name I am using is xxxxxxx.onmicrosoft.com, is that correct? I'm am trying to authenticate using my windows credentials, which is my Hotmail account. But doesn't seem to want to let me connect, or if I choose alternate credentials and enter my Hotmail account details.

Describe the bug
I was able to install the Infrastructure and Governance dashboard, but when it comes to the AKS dashboard, I have not been successful in installing it. The bug is with the refresh stage, where there is an error somewhere in the M Code.

To Reproduce
Steps to reproduce the behavior:

1. Go to 'ccodashboard\dashboards\CCODashboard-AKS '
2. Open 'CCO AKS Dashboard 5.1'
3. Click 'Refresh All'
4. See error

Expected behavior
Data is expected to be fetched, which it actually is, as I can see it in the advanced editor, but the overall refresh fails, as can be seen in the screen shot

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [win10]
• App: PowerBI Desktop
• Version [N/A]

Session log

Additional context
Add any other context about the problem here.

I followed the instructions to install and configure PowerBI.
When I initially try to connect, the connection works and then I get the following error:

Error Message:
Object reference not set to an instance of an object.

Stack Trace:
at Microsoft.Mashup.Evaluator.ChannelMessenger.CreateChannel()
at Microsoft.PowerBI.Client.OleDbProvider.PowerBIMashupDataSource.EvaluationSession.Dispose()
at Microsoft.Mashup.Host.ProviderShared.PackageMashupEvaluator.<>c__DisplayClass7_0.b__1(Object o)
at Microsoft.Mashup.Evaluator.Interface.InvokeManyAction1.CheckDone() at Microsoft.Mashup.OleDbProvider.DataHost.MashupEvaluator.<>c__DisplayClass4_1.<Evaluate>b__1() at Microsoft.Mashup.OleDbProvider.DataHost.AsyncResultIDataReaderSourceHelper.InvokeThenOnDispose(Action1 callback, AsyncResult1 result, Action action) at Microsoft.Mashup.OleDbProvider.DataHost.MashupEvaluator.<>c__DisplayClass4_0.<Evaluate>b__0(EvaluationResult21 result)
at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose(Action1 callback, EvaluationResult21 result, Action action)
at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose[T](Action1 callback, EvaluationResult21 result, Action action)
at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation1.Complete(EvaluationResult21 result)
at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation`1.Cancel()
at Microsoft.Mashup.Evaluator.Interface.CompositeEvaluation.Cancel()
at Microsoft.Mashup.Host.ProviderShared.NotifyingMashupEvaluator.MashupEvaluation.Cancel()
at Microsoft.Mashup.OleDbProvider.MashupRowset.AbortIfNotComplete(Boolean timedOut)
at Microsoft.Mashup.OleDbProvider.MashupRowset.Microsoft.OleDb.IDBAsynchStatus.Abort(HCHAPTER hChapter, DBASYNCHOP eOperation)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IDBAsynchStatus.Abort(HCHAPTER hChapter, DBASYNCHOP eOperation)

Stack Trace Message:
Object reference not set to an instance of an object.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass15_0.b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.b__0(Object null)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func1 showModalFunction) at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IQueryServices queryServices, IModelingService modelingService, IUIHost uiHost, LocalizedString title, LoadToModelContext loadToModelContext, IEventAggregationService eventAggregationService) at Microsoft.PowerBI.Client.Windows.Services.WarningDetectionService.<DetectReportLoadWarning>b__28_1() at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action) at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Delegate.DynamicInvokeImpl(Object[] args) at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme) at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme) at System.Windows.Forms.Control.InvokeMarshaledCallbacks() at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous) at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args) at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state) at Microsoft.PowerBI.Client.Windows.Services.WarningNotificationService.ExecuteOnUiThread(Action action) at Microsoft.PowerBI.Client.Windows.Services.WarningAction.<>c__DisplayClass17_0.<ToFunction>b__0() at Microsoft.PowerBI.Client.Windows.Services.WarningAction.ExecuteAndCompleteTask(Func1 func)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass1_0.

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.85.442.0","ModuleName":"","Component":"Microsoft.Mashup.Evaluator.ChannelMessenger","Error":"Microsoft.Mashup.Host.Document.SerializedException - System.NullReferenceException","MethodDef":"CreateChannel","ErrorOffset":""}

Snapshot Trace Logs:
C:\Users\bahramr\Microsoft\Power BI Desktop Store App\FrownSnapShot453928252.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Is Report V3 Models Enabled:
True

Performance Trace Logs:
C:\Users\bahramr\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_NewWebTableInference

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_ImportTextByExample
PBI_qnaLiveConnect
PBI_inlineExplore
PBI_dataSearchCuration
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_narrativeTextBox

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
MSIT

PowerBINonFatalError_ErrorDescription:
System.NullReferenceException

PowerBIUserFeedbackServices_IsReported:
True

Formulas:

section Section1;

shared SecureScore = let
Source = SecureScoreControls_F
in
Source;

shared SecureScoreControlsDefinitions = let ListSecureScoreControlsDefinitions = (SubscriptionId as text) =>

let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/providers/Microsoft.Security/SecureScoreControlDefinitions?api-version=2020-01-01-preview"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "type", "properties"}, {"id", "name", "type", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"displayName", "maxScore", "source", "assessmentDefinitions"}, {"displayName", "maxScore", "source", "assessmentDefinitions"}),
#"Expanded source" = Table.ExpandRecordColumn(#"Expanded properties", "source", {"sourceType"}, {"sourceType"}),
#"Expanded assessmentsDefinitions" = Table.ExpandListColumn(#"Expanded source", "assessmentDefinitions"),
#"Expanded assessmentsDefinitions1" = Table.ExpandRecordColumn(#"Expanded assessmentsDefinitions", "assessmentDefinitions", {"id"}, {"id.1"})
in
#"Expanded assessmentsDefinitions1"

in
ListSecureScoreControlsDefinitions;

shared SecureScoreControlDefinitions_F = let
Source = #"All Subscriptions",
#"Removed Columns" = Table.RemoveColumns(Source,{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"tenantId", "Subscription Name", "subscriptionId"}),
#"Invoked Custom Function" = Table.AddColumn(#"Reordered Columns", "SecureScoreControlDefinitions", each SecureScoreControlsDefinitions([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"SecureScoreControlDefinitions"}),
#"Expanded SecureScoreControlDefinitions1" = Table.ExpandTableColumn(#"Removed Errors", "SecureScoreControlDefinitions", {"id", "name", "type", "displayName", "maxScore", "sourceType", "id.1"}, {"id", "name", "type", "displayName", "maxScore", "sourceType", "id.1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded SecureScoreControlDefinitions1", "id.1", Splitter.SplitTextByDelimiter("/providers", QuoteStyle.Csv), {"id.1", "id.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"id.1", type text}, {"id.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type",{"id.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"id.2", "SecureScoreControlDefinitions.id"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"maxScore", type number}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "SecureScoreControlDefinitions.id", "SecureScoreControlDefinitions.assessmentsDefinition - Copy"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column", "SecureScoreControlDefinitions.assessmentsDefinition - Copy", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"SecureScoreControlDefinitions.assessmentsDefinition - Copy.1", "SecureScoreControlDefinitions.assessmentsDefinition - Copy.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"SecureScoreControlDefinitions.assessmentsDefinition - Copy.1", type text}, {"SecureScoreControlDefinitions.assessmentsDefinition - Copy.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"SecureScoreControlDefinitions.assessmentsDefinition - Copy.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"SecureScoreControlDefinitions.assessmentsDefinition - Copy.2", "Assesstment name"}}),
#"Removed Duplicates1" = Table.Distinct(#"Renamed Columns1", {"Assesstment name"}),
#"Removed Columns3" = Table.RemoveColumns(#"Removed Duplicates1",{"Subscription Resource Id","tenantId", "Subscription Name", "subscriptionId"})
in
#"Removed Columns3";

shared SecureScoreControls_F = let
Source = #"All Subscriptions",
#"Removed Columns" = Table.RemoveColumns(Source,{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"tenantId", "Subscription Name", "subscriptionId"}),
#"Invoked Custom Function" = Table.AddColumn(#"Reordered Columns", "SecuirtyScoreControls", each SecureScoreControls([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"SecuirtyScoreControls"}),
#"Expanded SecuirtyScoreControls" = Table.ExpandTableColumn(#"Removed Errors", "SecuirtyScoreControls", {"id", "name", "displayName", "score.max", "score.current", "healthyResourceCount", "unhealthyResourceCount", "notApplicableResourceCount"}, {"id", "name", "displayName", "score.max", "score.current", "healthyResourceCount", "unhealthyResourceCount", "notApplicableResourceCount"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded SecuirtyScoreControls",{{"id", "SecureControl Id"}, {"name", "SecureControl Name"}, {"displayName", "SecureControl Display Name"}})
in
#"Renamed Columns";

shared SecureScoreControls = let ListSecureScoreControls= (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/providers/Microsoft.Security/SecureScoreControls?api-version=2020-01-01-preview"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "type", "properties"}, {"id", "name", "type", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"displayName", "score", "healthyResourceCount", "unhealthyResourceCount", "notApplicableResourceCount"}, {"displayName", "score", "healthyResourceCount", "unhealthyResourceCount", "notApplicableResourceCount"}),
#"Expanded score" = Table.ExpandRecordColumn(#"Expanded properties", "score", {"max", "current"}, {"score.max", "score.current"})
in
#"Expanded score"
in ListSecureScoreControls;

shared #"CC Applied Policies" = let
Source = CcoDashboardAzureConnector.Management(AzureKind),
policies = Source{[Key="policies"]}[Data],
#"Expanded Value" = Table.ExpandTableColumn(policies, "Value", {"Column1"}, {"Column1"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Value", {"Column1"}),
#"Expanded Column1" = Table.ExpandListColumn(#"Removed Errors", "Column1"),
#"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"timestamp", "resourceId", "policyAssignmentId", "policyDefinitionId", "effectiveParameters", "isCompliant", "resourceType", "resourceLocation", "resourceGroup", "policyAssignmentName", "policyAssignmentOwner", "policyAssignmentParameters", "policyAssignmentScope", "policyDefinitionName", "policyDefinitionAction", "policyDefinitionCategory", "policySetDefinitionId", "policySetDefinitionName", "policySetDefinitionOwner", "policySetDefinitionCategory", "policySetDefinitionParameters", "managementGroupIds", "policyDefinitionReferenceId", "complianceState"}, {"timestamp", "resourceId", "policyAssignmentId", "policyDefinitionId", "effectiveParameters", "isCompliant", "resourceType", "resourceLocation", "resourceGroup", "policyAssignmentName", "policyAssignmentOwner", "policyAssignmentParameters", "policyAssignmentScope", "policyDefinitionName", "policyDefinitionAction", "policyDefinitionCategory", "policySetDefinitionId", "policySetDefinitionName", "policySetDefinitionOwner", "policySetDefinitionCategory", "policySetDefinitionParameters", "managementGroupIds", "policyDefinitionReferenceId", "complianceState"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Column3", each [timestamp] <> null and [timestamp] <> ""),
#"Lowercased Text" = Table.TransformColumns(#"Filtered Rows1",{{"policyAssignmentScope", Text.Lower, type text}}),
#"Added Custom" = Table.AddColumn(#"Lowercased Text", "Policy Image", each "https://raw.githubusercontent.com/CristianEdwards/ccodashboard/master/docs/assets/2020_Icons/Policy.svg"),
#"Added Conditional Column1" = Table.AddColumn(#"Added Custom", "ComplianceState Number", each if [complianceState] = "Compliant" then 1 else 0),
#"Added Conditional Column" = Table.AddColumn(#"Added Conditional Column1", "Policy Scope", each if Text.Contains([policyAssignmentScope], "managementgroup") then "Management Group" else if Text.Contains([policyAssignmentScope], "resourcegroup") then "Resource Group" else "Subscription")
in
#"Added Conditional Column";

shared ListPolicySets = let listPolicySets = (SubscriptionId as text) =>
let
Source = Json.Document(Web.Contents(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/providers/Microsoft.Authorization/policySetDefinitions?api-version=2019-09-01")),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"

in listPolicySets;

shared PolicyDefinitions = let
#"Invoked Custom Function" = Table.AddColumn(#"All Subscriptions", "Definitions", each ListPolicyDefinitions([subscriptionId])),
#"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Definitions"}),
#"Expanded Definitions" = Table.ExpandTableColumn(#"Removed Errors", "Definitions", {"displayName", "policyType", "mode", "description", "metadata.category", "metadata.additionalMetadataId", "metadata.preview", "metadata.deprecated", "metadata.requiredProviders", "policyRule", "parameters.effect.defaultValue", "id", "type", "name"}, {"Definitions.displayName", "Definitions.policyType", "Definitions.mode", "Definitions.description", "Definitions.metadata.category", "Definitions.metadata.additionalMetadataId", "Definitions.metadata.preview", "Definitions.metadata.deprecated", "Definitions.metadata.requiredProviders", "Definitions.policyRule", "Definitions.parameters.effect.defaultValue", "Definitions.id", "Definitions.type", "Definitions.name"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Definitions",null,false,Replacer.ReplaceValue,{"Definitions.metadata.preview"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,false,Replacer.ReplaceValue,{"Definitions.metadata.deprecated"}),
#"Removed Duplicates" = Table.Distinct(#"Replaced Value1", {"Definitions.id"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Duplicates", "Preview?", each if [Definitions.metadata.preview] = false then 0 else 1),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Deprecated", each if [Definitions.metadata.deprecated] = false then 0 else 1),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Deprecated", Int64.Type}, {"Preview?", Int64.Type}}),
#"Added Conditional Column2" = Table.AddColumn(#"Changed Type", "Image", each if [Definitions.displayName] <> null then "https://raw.githubusercontent.com/josunefon/ccodashboard/master/install/DI_Images/Azure_Policy.png" else "https://raw.githubusercontent.com/josunefon/ccodashboard/master/install/DI_Images/Azure_Policy.png")
in
#"Added Conditional Column2";

shared AllBuiltInPoliciesDefinition = let
Source = Json.Document(Web.Contents(GetManagementURL(AzureKind)&"/providers/Microsoft.Authorization/policyDefinitions?api-version=2019-09-01")),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"properties", "id", "name"}, {"properties", "id", "name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1",{"id", "name", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Reordered Columns", "properties", {"displayName", "policyType", "mode", "description", "metadata"}, {"displayName", "policyType", "mode", "description", "metadata"}),
#"Expanded metadata" = Table.ExpandRecordColumn(#"Expanded properties", "metadata", {"version", "category", "deprecated", "preview"}, {"version", "category", "deprecated", "preview"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded metadata",{{"id", "Policy Definition Id"}, {"name", "Policy Name"}, {"displayName", "Policy DisplayName"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([policyType] = "BuiltIn")),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Image", each if [category] = "Key Vault" then "https://azure.github.io/ccodashboard/assets/2020_Icons/KeyVaults.svg" else if [category] = "Storage" then "https://azure.github.io/ccodashboard/assets/2020_Icons/StorageAccounts.svg" else if [category] = "SQL" then "https://azure.github.io/ccodashboard/assets/2020_Icons/SQLdatabases.svg" else if [category] = "Compute" then "https://azure.github.io/ccodashboard/assets/2020_Icons/VirtualMachines.svg" else if [category] = "Security Center" then "https://azure.github.io/ccodashboard/assets/2020_Icons/SecurityCenter.svg" else if [category] = "App Service" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AppServices.svg" else if [category] = "Logic Apps" then "https://azure.github.io/ccodashboard/assets/2020_Icons/LogicApps.svg" else if [category] = "Monitoring" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Monitor.svg" else if [category] = "Guest Configuration" then "https://azure.github.io/ccodashboard/assets/2020_Icons/GuestConfig.svg" else if [category] = "Data Lake" then "https://azure.github.io/ccodashboard/assets/2020_Icons/DataLake.svg" else if [category] = "Backup" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Backup.svg" else if [category] = "Cognitive Services" then "https://azure.github.io/ccodashboard/assets/2020_Icons/CognitiveServices.svg" else if [category] = "Cosmos DB" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AzureCosmosDB.svg" else if [category] = "Tags" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Tags.svg" else if [category] = "Service Fabric" then "https://azure.github.io/ccodashboard/assets/2020_Icons/ServiceFabric.svg" else if [category] = "General" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Subscriptions.svg" else if [category] = "Managed Application" then "https://azure.github.io/ccodashboard/assets/2020_Icons/ManagedApplications.svg" else if [category] = "Network" then "https://azure.github.io/ccodashboard/assets/2020_Icons/VirtualNetworks.svg" else if [category] = "Automation" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AutomationAccounts.svg" else if [category] = "Internet of Things" then "https://azure.github.io/ccodashboard/assets/2020_Icons/IoT.svg" else if [category] = "Event Grid" then "https://azure.github.io/ccodashboard/assets/2020_Icons/EventGrid.svg" else if [category] = "Search" then "https://azure.github.io/ccodashboard/assets/2020_Icons/SearchServices.svg" else if [category] = "App Configuration" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AppConfiguration.svg" else if [category] = "App Platform" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AzureSpringCloud.svg" else if [category] = "Cache" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AzureCacheRedis.svg" else if [category] = "Batch" then "https://azure.github.io/ccodashboard/assets/2020_Icons/BatchAccounts.svg" else if [category] = "Container Registry" then "https://azure.github.io/ccodashboard/assets/2020_Icons/ContainerRegistries.svg" else if [category] = "Lighthouse" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AzureLighthouse.svg" else if [category] = "Event Hub" then "https://azure.github.io/ccodashboard/assets/2020_Icons/EventHubs.svg" else if [category] = "Service Bus" then "https://azure.github.io/ccodashboard/assets/2020_Icons/ServiceBus.svg" else if [category] = "Custom Provider" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Resource.svg" else if [category] = "API Management" then "https://azure.github.io/ccodashboard/assets/2020_Icons/APIManagement.svg" else if [category] = "Kubernetes" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Kubernetesservices.svg" else if [category] = "Kubernetes service" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Kubernetesservices.svg" else if [category] = "Machine Learning" then "https://azure.github.io/ccodashboard/assets/2020_Icons/MachineLearning.svg" else if [category] = "Stream Analytics" then "https://azure.github.io/ccodashboard/assets/2020_Icons/StreamAnalytics.svg" else null)
in
#"Added Conditional Column";

shared ListPolicyDefinitions = let ListAllPolicies = (subscriptionId as text) =>
let
Source = Json.Document(Web.Contents(GetManagementURL(AzureKind)&"/subscriptions/"&subscriptionId&"/providers/Microsoft.Authorization/policyDefinitions?api-version=2019-09-01")),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"properties", "id", "type", "name"}, {"properties", "id", "type", "name"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"displayName", "policyType", "mode", "description", "metadata", "policyRule", "parameters"}, {"displayName", "policyType", "mode", "description", "metadata", "policyRule", "parameters"}),
#"Expanded metadata" = Table.ExpandRecordColumn(#"Expanded properties", "metadata", {"category", "additionalMetadataId", "preview", "deprecated", "requiredProviders"}, {"metadata.category", "metadata.additionalMetadataId", "metadata.preview", "metadata.deprecated", "metadata.requiredProviders"}),
#"Expanded parameters" = Table.ExpandRecordColumn(#"Expanded metadata", "parameters", {"effect"}, {"parameters.effect"}),
#"Expanded parameters.effect" = Table.ExpandRecordColumn(#"Expanded parameters", "parameters.effect", {"defaultValue"}, {"parameters.effect.defaultValue"})
in
#"Expanded parameters.effect"
in
ListAllPolicies;

shared Resources = let
#"Invoked Custom Function" = Table.AddColumn(#"All Subscriptions", "ListOfResources", each ListResourcesFinal([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"ListOfResources"}),
#"Expanded ListOfResources" = Table.ExpandTableColumn(#"Removed Errors", "ListOfResources", {"Resource Id", "Resource Name", "Resource Type", "location", "IsTagged", "Resource Group Id", "Resource Group Name"}, {"Resource Id", "Resource Name", "Resource Type", "location", "IsTagged", "Resource Group Id", "Resource Group Name"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded ListOfResources", each [Resource Id] <> null and [Resource Id] <> ""),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "IsTagged?", each if [IsTagged] = "Tagged" then 1 else 0),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Conditional Column", "Resource Type", "Resource Type - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Resource Type - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Resource Type - Copy.1", "Resource Type - Copy.2", "Resource Type - Copy.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resource Type - Copy.1", type text}, {"Resource Type - Copy.2", type text}, {"Resource Type - Copy.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Resource Type - Copy.1", "Resource Type - Copy.3"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Resource Type - Copy.2", "Resource Type for Usage"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"})
in
#"Removed Columns1";

shared ListAssessments = let ListAllAssesssments = (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/providers/Microsoft.Security/assessments?api-version=2020-01-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "id", "name", "properties"}, {"type", "id", "name", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"resourceDetails", "displayName", "status", "additionalData"}, {"resourceDetails", "displayName", "status", "additionalData"}),
#"Expanded resourceDetails" = Table.ExpandRecordColumn(#"Expanded properties", "resourceDetails", {"Source", "Id"}, {"resourceDetails.Source", "resourceDetails.Id"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Expanded resourceDetails", "status", {"code", "cause", "description"}, {"status.code", "status.cause", "status.description"})
in
#"Expanded status"
in ListAllAssesssments;

shared ASC_Assessments = let
Source = #"All Subscriptions",
#"Removed Columns" = Table.RemoveColumns(Source,{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"tenantId", "Subscription Name", "subscriptionId"}),
#"Invoked Custom Function" = Table.AddColumn(#"Reordered Columns", "Assesstments", each ListAssessments([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Assesstments"}),
#"Expanded Assesstments" = Table.ExpandTableColumn(#"Removed Errors", "Assesstments", {"id", "name", "resourceDetails.Source", "resourceDetails.Id", "displayName", "status.code", "status.cause", "status.description"}, {"Assesstments.id", "Assesstments.name", "Assesstments.resourceDetails.Source", "Assesstments.resourceDetails.Id", "Assesstments.displayName", "Assesstments.status.code", "Assesstments.status.cause", "Assesstments.status.description"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Assesstments", "Assesstments.id", "Assesstments.id - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Assesstments.id - Copy", Splitter.SplitTextByDelimiter("providers", QuoteStyle.Csv), {"Assesstments.id - Copy.1", "Assesstments.id - Copy.2", "Assesstments.id - Copy.3"}),
#"Added Conditional Column1" = Table.AddColumn(#"Split Column by Delimiter", "Assessment Id Final", each if [#"Assesstments.id - Copy.3"] = null then [#"Assesstments.id - Copy.2"] else [#"Assesstments.id - Copy.3"]),
#"Removed Columns3" = Table.RemoveColumns(#"Added Conditional Column1",{"Assesstments.id - Copy.2", "Assesstments.id - Copy.3", "Assesstments.id - Copy.1"}),
#"Lowercased Text" = Table.TransformColumns(#"Removed Columns3",{{"Assesstments.resourceDetails.Id", Text.Lower, type text}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Lowercased Text", "Assesstments.resourceDetails.Id", "Assesstments.resourceDetails.Id - Copy"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column1", "Assesstments.resourceDetails.Id - Copy", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Assesstments.resourceDetails.Id - Copy.1", "Assesstments.resourceDetails.Id - Copy.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Assesstments.resourceDetails.Id - Copy.2", "ResourceName"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Assesstments.resourceDetails.Id - Copy.1"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "ResourceScore", each if [Assesstments.status.code] = "Unhealthy" then 1 else if [Assesstments.status.code] = "NotApplicable" then 10 else 0),
#"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column",{"Assesstments.id"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns2", each ([Assesstments.resourceDetails.Source] = "Azure")),
#"Added Conditional Column2" = Table.AddColumn(#"Filtered Rows", "Image", each if [Assesstments.status.code] = "Healthy" then "https://azure.github.io/ccodashboard/assets/pictures/checkmark.svg" else if [Assesstments.status.code] = "NotApplicable" then "https://azure.github.io/ccodashboard/assets/pictures/crossout.svg" else "https://azure.github.io/ccodashboard/assets/pictures/crossout.svg")
in
#"Added Conditional Column2";

shared assessmentMetadata_F = let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/providers/Microsoft.Security/assessmentMetadata?api-version=2020-01-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Converted to Table", {"Column1"}),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Removed Errors", "Column1", {"id", "name", "type", "properties"}, {"id", "name", "type", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"displayName", "assessmentType", "policyDefinitionId", "description", "remediationDescription", "categories", "preview", "severity", "userImpact", "implementationEffort", "threats"}, {"displayName", "assessmentType", "policyDefinitionId", "description", "remediationDescription", "categories", "preview", "severity", "userImpact", "implementationEffort", "threats"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded properties", {"categories", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values";

shared Blueprints = let
Source = CcoDashboardAzureConnector.Management(AzureKind),
blueprints = Source{[Key="blueprints"]}[Data],
#"Removed Columns" = Table.RemoveColumns(blueprints,{"id", "tenantId", "countryCode", "displayName", "domains", "tenantCategory", "ManagementGroups.count"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"scopeId"}),
#"Expanded Blueprints" = Table.ExpandTableColumn(#"Removed Duplicates", "Blueprints", {"Column1"}, {"Column1"}),
#"Expanded Column1" = Table.ExpandListColumn(#"Expanded Blueprints", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"properties", "id", "name"}, {"properties", "id", "name"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column2", each [id] <> null and [id] <> ""),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"id", "Blueprint Id"}, {"name", "Blueprint Custom Name"}}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Renamed Columns", "properties", {"targetScope", "status", "displayName", "description"}, {"targetScope", "status", "displayName", "description"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded properties",{{"displayName", "Blueprint Official Name"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",null,"Custom Blueprint",Replacer.ReplaceValue,{"Blueprint Official Name"}),
#"Renamed Columns2" = Table.RenameColumns(#"Replaced Value",{{"description", "Blueprint Descriptions"}}),
#"Expanded status" = Table.ExpandRecordColumn(#"Renamed Columns2", "status", {"timeCreated", "lastModified"}, {"timeCreated", "lastModified"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded status",{{"scopeId", "Blueprint Store and Scope"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns3", "Store Type", each if Text.Contains([Blueprint Store and Scope], "managementGroups") then "Management Group" else "Subscription"),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Img", each if [Store Type] = "Subscription" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Subscriptions.svg" else "https://azure.github.io/ccodashboard/assets/2020_Icons/ManagementGroups.svg")
in
#"Added Conditional Column1";

shared #"Published Blueprints" = let
Source = CcoDashboardAzureConnector.Management(AzureKind),
blueprintspublished = Source{[Key="blueprintspublished"]}[Data],
#"Renamed Columns" = Table.RenameColumns(blueprintspublished,{{"id.1", "Published Blueprint Version Id"}, {"description", "Published Blueprint Description"}, {"name.2", "Published Blueprint Version"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"parameters", "resourceGroups"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Removed Columns", "status", {"timeCreated", "lastModified"}, {"timeCreated", "lastModified"}),
#"Removed Columns3" = Table.RemoveColumns(#"Expanded status",{"properties"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Columns3",{"Name.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"id", "Blueprint Id"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"type.1"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns2", {"Blueprint Id"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Duplicates",{{"displayName.1", "Blueprint Portal Name"}}),
#"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{{"scopeId", "Blueprint Store"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns3",null,"NOT PUBLISHED",Replacer.ReplaceValue,{"blueprintName"}),
#"Renamed Columns4" = Table.RenameColumns(#"Replaced Value",{{"blueprintName", "Published Blueprint Name"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns4",null,"Draft",Replacer.ReplaceValue,{"Published Blueprint Version"})
in
#"Replaced Value1";

shared #"Blueprint Artifacts" = let
Source = CcoDashboardAzureConnector.Management(AzureKind),
blueprintartifact = Source{[Key="blueprintartifact"]}[Data],
#"Renamed Columns" = Table.RenameColumns(blueprintartifact,{{"id.1", "Blueprint Artifact Id"}, {"name.1", "Blueprint Artifact Name"}, {"id", "Blueprint Id"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"tenantId", "displayName", "scopeId", "properties", "type"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"name", "Blueprint Custom Name"}}),
#"Expanded properties.1" = Table.ExpandRecordColumn(#"Renamed Columns1", "properties.1", {"policyDefinitionId"}, {"policyDefinitionId"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded properties.1",{"type.1"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Img", each if [kind] = "policyAssignment" then "https://azure.github.io/ccodashboard/assets/2020_Icons/Policy.svg" else if [kind] = "template" then "https://azure.github.io/ccodashboard/assets/2020_Icons/BlueprintTemplateKind.svg" else if [kind] = "roleAssignment" then "https://azure.github.io/ccodashboard/assets/2020_Icons/BlueprintRoleContributorKind.svg" else null),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [Blueprint Artifact Id] <> null and [Blueprint Artifact Id] <> "")
in
#"Filtered Rows";

shared ListBlueprintAssignments = let ListBlueprintAssignments = (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/providers/Microsoft.Blueprint/blueprintAssignments?api-version=2018-11-01-preview"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"identity", "location", "properties", "id", "name"}, {"identity", "location", "properties", "id", "name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Column1",{{"id", "Assigned Blueprint Id"}, {"name", "Assigned Blueprint Name"}}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Renamed Columns1", "properties", {"provisioningState", "blueprintId", "parameters", "resourceGroups", "status", "scope", "locks"}, {"provisioningState", "blueprintId", "parameters", "resourceGroups", "status", "scope", "locks"})
in
#"Expanded properties"
in
ListBlueprintAssignments;

shared #"Resource Groups" = let
Source = #"All Subscriptions",
#"Invoked Custom Function" = Table.AddColumn(#"All Subscriptions", "ListOfResourceGroups", each ListResourceGroups([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"ListOfResourceGroups"}),
#"Expanded ListOfResourceGroups" = Table.ExpandTableColumn(#"Removed Errors", "ListOfResourceGroups", {"Resource Group Id", "Resource Group Name", "type", "location", "IsTagged"}, {"Resource Group Id", "Resource Group Name", "type", "location", "IsTagged"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded ListOfResourceGroups", "IsTaggedNumeric", each if [IsTagged] = "Tagged" then 1 else 0),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"IsTaggedNumeric", "IsTagged?"}})
in
#"Renamed Columns1";

shared ListResourceGroups = let ListResourceGroups = (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/resourcegroups?api-version=2019-05-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "type", "location", "tags"}, {"id", "name", "type", "location", "tags"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"id", "Resource Group Id"}, {"name", "Resource Group Name"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "IsTagged", each if [tags] = null then "Untagged" else if [tags] = [] then "Untagged" else "Tagged"),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"tags"})
in
#"Removed Columns"

in
ListResourceGroups;

shared ListResourcesFinal = let ListResources= (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/resources?api-version=2019-05-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "type", "location", "tags"}, {"id", "name", "type", "location", "tags"}),

#"Added Conditional Column" = Table.AddColumn(#"Expanded Column2", "IsTagged", each if [tags] = null then "Untagged" else if [tags] = [] then "Untagged" else "Tagged"),

#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"tags"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"id", "Resource Id"}, {"name", "Resource Name"}, {"type", "Resource Type"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Resource Id", "Resource Id - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Resource Id - Copy", Splitter.SplitTextByDelimiter("/providers", QuoteStyle.Csv), {"Resource Id - Copy.1", "Resource Id - Copy.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resource Id - Copy.1", type text}, {"Resource Id - Copy.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Resource Id - Copy.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Resource Id - Copy.1", "Resource Group Id"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns1", "Resource Group Id", "Resource Group Id - Copy"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column1", "Resource Group Id - Copy", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Resource Group Id - Copy.1", "Resource Group Id - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Resource Group Id - Copy.1", type text}, {"Resource Group Id - Copy.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Resource Group Id - Copy.2", "Resource Group Name"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns2",{"Resource Group Id - Copy.1"})

in
#"Removed Columns2"
in
ListResources;

shared TaggedResourceGroups = let
Source = #"All Subscriptions",
#"Removed Columns" = Table.RemoveColumns(Source,{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "ListResourceGroupsTags", each ListResourceGroupsTags([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"ListResourceGroupsTags"}),
#"Expanded ListResourceGroupsTags" = Table.ExpandTableColumn(#"Removed Errors", "ListResourceGroupsTags", {"id", "TagKey", "TagValue"}, {"id", "TagKey", "TagValue"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded ListResourceGroupsTags", "id", "Resource Group Id - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Resource Group Id - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Resource Group Id - Copy.1", "Resource Group Id - Copy.2", "Resource Group Id - Copy.3", "Resource Group Id - Copy.4", "Resource Group Id - Copy.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resource Group Id - Copy.1", type text}, {"Resource Group Id - Copy.2", type text}, {"Resource Group Id - Copy.3", type text}, {"Resource Group Id - Copy.4", type text}, {"Resource Group Id - Copy.5", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Resource Group Id - Copy.1", "Resource Group Id - Copy.2", "Resource Group Id - Copy.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Resource Group Id - Copy.4", "Resource Type"}, {"Resource Group Id - Copy.5", "Resource Group Name"}})
in
#"Renamed Columns";

shared ListResourceGroupsTags = let ListResourceGroupsTags = (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/resourcegroups?api-version=2019-05-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name","tags"}, {"id", "name","tags"}),
ColumnContents = Table.Column(#"Expanded Column1", "tags"),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is record then Record.FieldNames(_) else {}))),
#"Expanded tags"= Table.ExpandRecordColumn(#"Expanded Column1", "tags", ColumnsToExpand, ColumnsToExpand),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded tags", {"id", "name"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "TagKey"}, {"Value", "TagValue"}, {"name", "Resource Name"}})
in
#"Renamed Columns"
in
ListResourceGroupsTags;

shared GetManagementURL = let GetManagementBaseUri=(govKind as text) as text =>
let
managementUrl = if govKind ="us-government" then "https://management.usgovcloudapi.net"
else if govKind="germany-government" then""
else if govKind = "china" then ""
else "https://management.azure.com"

in
    managementUrl

in GetManagementBaseUri;

shared AzureKind = "us-government" meta [IsParameterQuery=true, List={"us-government", "global"}, DefaultValue="global", Type="Text", IsParameterQueryRequired=true];

shared GetGraphURL = let GetGraphBaseUri=(govKind as text) as text =>
let
GraphURL = if govKind ="us-government" then "https://graph.microsoft.us"
else if govKind="germany-government" then""
else if govKind = "china" then ""
else "https://graph.windows.net"

in
    GraphURL

in GetGraphBaseUri;

shared #"All Subscriptions" = let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions?api-version=2020-01-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "authorizationSource", "managedByTenants", "subscriptionId", "tenantId", "displayName", "state", "subscriptionPolicies", "tags"}, {"id", "authorizationSource", "managedByTenants", "subscriptionId", "tenantId", "displayName", "state", "subscriptionPolicies", "tags"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"managedByTenants"}),
#"Expanded subscriptionPolicies" = Table.ExpandRecordColumn(#"Removed Columns", "subscriptionPolicies", {"locationPlacementId", "quotaId", "spendingLimit"}, {"locationPlacementId", "quotaId", "spendingLimit"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded subscriptionPolicies",{{"displayName", "Subscription Name"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"tags"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"id", "Subscription Resource Id"}})
in
#"Renamed Columns1";

shared #"CC GetEntities" = let
Source = CcoDashboardAzureConnector.Management(AzureKind),
alldescendantstrick = Source{[Key="alldescendantstrick"]}[Data],
#"Expanded ManagementGroups" = Table.ExpandRecordColumn(alldescendantstrick, "ManagementGroups", {"value"}, {"value"}),
#"Expanded value" = Table.ExpandListColumn(#"Expanded ManagementGroups", "value"),
#"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"name", "id", "type", "properties"}, {"name", "id.1", "type", "properties"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded value1",{{"id.1", "Resource Id"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [name] <> null and [name] <> ""),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"name", "Resource Name"}, {"type", "Resource Type"}}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Renamed Columns1", "properties", {"numberOfChildren", "numberOfChildGroups", "numberOfDescendants", "displayName", "parentDisplayNameChain", "inheritedPermissions", "permissions"}, {"numberOfChildren", "numberOfChildGroups", "numberOfDescendants", "displayName.1", "parentDisplayNameChain", "inheritedPermissions", "permissions"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded properties",{{"displayName.1", "Resource Display Name"}}),
#"Extracted Values" = Table.TransformColumns(#"Renamed Columns2", {"parentDisplayNameChain", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "parentDisplayNameChain", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"parentDisplayNameChain.1", "parentDisplayNameChain.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"parentDisplayNameChain.1", type text}, {"parentDisplayNameChain.2", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "TempParentDisplayName", each if [parentDisplayNameChain.1] = "" then "IsRoot" else [parentDisplayNameChain.2]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Parent Display Name", each if [TempParentDisplayName] = null then [parentDisplayNameChain.1] else [TempParentDisplayName]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column1",{"parentDisplayNameChain.1", "parentDisplayNameChain.2", "TempParentDisplayName"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","Is Tenant Root Group",null,Replacer.ReplaceValue,{"Parent Display Name"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1),
#"Added Conditional Column2" = Table.AddColumn(#"Added Index", "Image", each if [Resource Type] = "/providers/Microsoft.Management/managementGroups" then "https://azure.github.io/ccodashboard/assets/pictures/037_Management_Groups.svg" else "https://azure.github.io/ccodashboard/assets/pictures/036_ASCTasks_Subscription.svg"),
#"Replaced Value1" = Table.ReplaceValue(#"Added Conditional Column2","IsRoot",null,Replacer.ReplaceValue,{"Parent Display Name"})
in
#"Replaced Value1";

shared ListAzureLocations = let ListAzureLocations = (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/locations?api-version=2020-01-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "displayName", "regionalDisplayName", "metadata"}, {"id", "name", "displayName", "regionalDisplayName", "metadata"}),
#"Expanded metadata" = Table.ExpandRecordColumn(#"Expanded Column1", "metadata", {"regionType", "regionCategory", "geographyGroup", "longitude", "latitude", "physicalLocation", "pairedRegion"}, {"regionType", "regionCategory", "geographyGroup", "longitude", "latitude", "physicalLocation", "pairedRegion"}),
#"Expanded pairedRegion" = Table.ExpandListColumn(#"Expanded metadata", "pairedRegion"),
#"Expanded pairedRegion1" = Table.ExpandRecordColumn(#"Expanded pairedRegion", "pairedRegion", {"name", "id"}, {"name.1", "id.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded pairedRegion1",{{"id", "Location Id"}, {"name", "Location Name"}, {"displayName", "Location Display Name"}, {"regionalDisplayName", "Location regionalDisplayName"}, {"name.1", "Paired Region Name"}, {"id.1", "Paired Region Id"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([regionType] = "Physical"))
in
#"Filtered Rows"
in
ListAzureLocations;

shared #"Azure Regions" = let
Source = #"All Subscriptions",
#"Removed Columns" = Table.RemoveColumns(Source,{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "ListAzureLocations", each ListAzureLocations([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"ListAzureLocations"}),
#"Expanded ListAzureLocations" = Table.ExpandTableColumn(#"Removed Errors", "ListAzureLocations", {"Location Id", "Location Name", "Location Display Name", "Location regionalDisplayName", "regionType", "regionCategory", "geographyGroup", "longitude", "latitude", "physicalLocation", "Paired Region Name", "Paired Region Id"}, {"Location Id", "Location Name", "Location Display Name", "Location regionalDisplayName", "regionType", "regionCategory", "geographyGroup", "longitude", "latitude", "physicalLocation", "Paired Region Name", "Paired Region Id"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded ListAzureLocations",{"Subscription Resource Id", "subscriptionId", "Subscription Name", "Location Id", "tenantId"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns1", {"Location Name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"longitude", type number}, {"latitude", type number}})
in
#"Changed Type";

shared #"Tagged Resources" = let
Source = #"All Subscriptions",
#"Removed Columns" = Table.RemoveColumns(Source,{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "ListResourceTags", each ListResourceTags([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"ListResourceTags"}),
#"Expanded ListResourceTags" = Table.ExpandTableColumn(#"Removed Errors", "ListResourceTags", {"id", "ResourceName", "TagKey", "TagValue"}, {"id", "ResourceName", "TagKey", "TagValue"})
in
#"Expanded ListResourceTags";

shared ListResourceTags = let ListResourceTags = (SubscriptionId as text) =>
let
GetPages = (Path)=>
let
Source = Json.Document(Web.Contents(Path)),
LL= @source[value],
result = try @ll & @GetPages(Source[#"nextLink"]) otherwise @ll
in
result,
Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions/"&SubscriptionId&"/resources?api-version=2019-10-01"),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn( #"Converted to Table", "Column1", {"id", "name", "tags"}, {"id", "name", "tags"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Column1",{{"name", "ResourceName"}}),
ColumnContents = Table.Column(#"Renamed Columns1", "tags"),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is record then Record.FieldNames(_) else {}))),
#"Expanded tags"= Table.ExpandRecordColumn(#"Renamed Columns1", "tags", ColumnsToExpand, ColumnsToExpand),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded tags", {"id", "ResourceName"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "TagKey"}, {"Value", "TagValue"}})
in
#"Renamed Columns"

in
ListResourceTags;

shared #"Blueprint Subscription Assignments" = let
Source = #"All Subscriptions",
#"Removed Columns" = Table.RemoveColumns(Source,{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "Blueprint Assignments", each ListBlueprintAssignments([subscriptionId])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Blueprint Assignments"}),
#"Expanded Blueprint Assignments" = Table.ExpandTableColumn(#"Removed Errors", "Blueprint Assignments", {"identity", "location", "provisioningState", "blueprintId", "parameters", "resourceGroups", "status", "scope", "locks", "Assigned Blueprint Id", "Assigned Blueprint Name"}, {"identity", "location", "provisioningState", "blueprintId", "parameters", "resourceGroups", "status", "scope", "locks", "Assigned Blueprint Id", "Assigned Blueprint Name"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Blueprint Assignments", "Img", each if [provisioningState] = "succeeded" then "https://azure.github.io/ccodashboard/assets/2020_Icons/AssignedBlueprints.svg" else "https://azure.github.io/ccodashboard/assets/2020_Icons/AssignedBlueprints.svg"),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"blueprintId", "Published Blueprint version Id"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Published Blueprint version Id", "Published Blueprint version Id - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Published Blueprint version Id - Copy", Splitter.SplitTextByDelimiter("/versions/", QuoteStyle.Csv), {"Published Blueprint version Id - Copy.1", "Published Blueprint version Id - Copy.2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Split Column by Delimiter",{{"Published Blueprint version Id - Copy.2", "Assigned Version"}}),
#"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns2",{{"Published Blueprint version Id - Copy.1", "Blueprint Id"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns1", "StateIcon", each if [provisioningState] = "failed" then 0 else 1),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"StateIcon", Int64.Type}})
in
#"Changed Type";

shared RegulatoryCompliancesPolicies = let
#"Invoked Custom Function" = Table.AddColumn(#"All Subscriptions", "ListPolicySets", each ListPolicySets([subscriptionId])),
#"Removed Columns5" = Table.RemoveColumns(#"Invoked Custom Function",{"authorizationSource", "state", "locationPlacementId", "quotaId", "spendingLimit"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns5", {"ListPolicySets"}),
#"Expanded ListPolicySets" = Table.ExpandTableColumn(#"Removed Errors", "ListPolicySets", {"Value"}, {"Value"}),
#"Expanded Value" = Table.ExpandListColumn(#"Expanded ListPolicySets", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"properties", "id", "name"}, {"properties", "id", "name"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Value1", "properties", {"displayName", "policyType", "description", "metadata", "policyDefinitions"}, {"displayName", "policyType", "description", "metadata", "policyDefinitions"}),
#"Expanded metadata" = Table.ExpandRecordColumn(#"Expanded properties", "metadata", {"version", "category"}, {"version", "category"}),
#"Expanded policyDefinitions" = Table.ExpandListColumn(#"Expanded metadata", "policyDefinitions"),
#"Expanded policyDefinitions1" = Table.ExpandRecordColumn(#"Expanded policyDefinitions", "policyDefinitions", {"policyDefinitionReferenceId", "policyDefinitionId"}, {"policyDefinitionReferenceId", "policyDefinitionId"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Expanded policyDefinitions1", "description", "description - Copy"),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Duplicated Column1", "description - Copy", Splitter.SplitTextByDelimiter("visit", QuoteStyle.Csv), {"description - Copy.1", "description - Copy.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"description - Copy.1", type text}, {"description - Copy.2", type text}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type2",{"description - Copy.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns4",{{"description - Copy.2", "URL"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "URL", "URL - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "URL - Copy", Splitter.SplitTextByDelimiter("https://aka.ms/", QuoteStyle.Csv), {"URL - Copy.1", "URL - Copy.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"URL - Copy.1", type text}, {"URL - Copy.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"URL - Copy.1"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Regulatory Standard Name", each if [#"URL - Copy.2"] = null then [displayName] else [#"URL - Copy.2"]),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each true),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"URL - Copy.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"id", "policySetDefinitionId"}, {"name", "policySetDefinitionName"}, {"displayName", "Regulatory Standard Long Name"}})
in
#"Renamed Columns1";

shared LastRefresh_Local = let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}}),
#"Renamed Columns" = Table.RenameColumns(Source,{{"LastRefresh", "Refresh"}})
in
#"Renamed Columns";

Describe the bug
When double clicking CCO Power BI Governance Dashboard v2.2.pbit I get the message "Unable to open document. The queries were authored with a newer version of Power Bi Desktop and might not work with your version"
To Reproduce
Steps to reproduce the behavior:
When double clicking CCO Power BI Governance Dashboard v2.2.pbit I get the message "Unable to open document. The queries were authored with a newer version of Power Bi Desktop and might not work with your version". After acknowledging this error I am taken through the authentication process and I connect to the global Azure instance. The query process then starts, before stopping with the error shown in the screenshot

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: Windows 10 (OS Build 19041.508)
• Browser Edge (Chrome)
  Power BI Desktop version 2.84.981.0 (from Microsoft Store)

Additional context
Add any other context about the problem here.

My Microsoft account is a guest in my customers tenant. I am an owner in the subscription associated with this tenant. When running the dashboard template I am prompted for the Tenant Name and I enter in the name of the customers tenant. However the only data that ever loads is the data associated with my Microsoft tenant.

Describe the bug
When counting the number of VMs in a subscription, it doesn't summ if there are VMs with the same name in different RGs in that subscription. So, in our case, it only shows 50 VMs in a subscription when there are over 200.

Expected behavior

In one of our subscriptions, we have several RGs with identical resources, so VMs have same name. So, all this VMs should be added to the VM count.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

The main dashboard CCO Power BI Dashboard looks great.
Is there is a way to set scheduled refresh for dataset in power bi?
See screenshot as seen in power bi cloud.(https://app.powerbi.com/groups/me/settings/datasets)

Is your feature request related to a problem? Please describe.
I have a request to limit views based on teams or level within the org. Eg: A manager's view where managers can only see things like Azure limits, Overview and Idle resources and a Operations view (for the ops team) where they can see everything within the dashboard.

Describe the solution you'd like
A way to implement RBAC and views based on Group or team

Describe alternatives you've considered
Set up 2 different dashboards and remove pages based on the requirement. However, this would make it harder to maintain.

Describe the bug
Following the initial startup guide, I get this error when trying to load the data into the dashboard for the first time. I get this error after sorting permissions for the management api and github. It becomes a cascading failure which I'm not able to resolve.

To Reproduce

1. Manually load advisor recommendations.
2. Clear existing permissions.
3. Load the template file and when prompted login to my organisational account.
4. Sort org level permissions for management api and github.
5. Data starts to load and then explosions happen.
6. Attempting to close the dialog and reload the data results cascading "Load was cancelled by an error in loading a previous table."

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):
Windows 10, Windows Edge (Chromium edition), Version 7.1 of the dashboard template.

The ability to query idle resources (disks, Network interfaces, public IPs, snapshots..etc) in to the dashboard along with the cost associated to these idle resource. I think a check box where you can filter between active resources and idle resources would be ideal.

• Overall cost per subscription
• Overall cost by resource group/location/Project billing code tag

Describe the bug
With the latest v7.0, when refreshing the dashboard there is an error stating duplicate subscription Id

Column 'subscriptionId' in Table 'All Subscriptions' contains a duplicate value 'xxxxxxxxx-769e-45ae-ab43-6da33674bd26' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

My fix was editing the query and removing duplicate subscription Ids.
#"Removed Duplicates" = Table.Distinct(#"Expanded managedByTenants1", {"subscriptionId"})

To Reproduce
A simple refresh of the dashboard (might not be reproduce able by everyone)

Expected behavior
Should pull every subscription Ids and remove duplicates

One of my customers would love to see a RI utilization component to the IaaS aspect of the CCO. There are APIs (https://docs.microsoft.com/en-us/rest/api/billing/enterprise/billing-enterprise-api-reserved-instance-usage) for this capability. Ideally there would also be a chart that illustrates how much IaaS is leveraging RI vs Pay as you go as well (by subscription or management group).

-Chris ([email protected])

Hi,

I have over 180 subscriptions, it appears that the management.azure.com/subscriptions API call is only retrieving 50 subscriptions. So I have extracted using powershell the full subscription list, and appended it into the AllSubscription table. However, when I refresh, whilst the number of subscriptions increases, the resources groups, virtual machines etc are not pulled from the full list of subscriptions in the AllSubscription table.

Is this expected behaviour? or can you advise how this could be resolved please.

Thanks

Colin

We are getting this issue with CCO v5 ASC: Data and storage recommendations
The column 'Resource type' of the table wasn't found.

Hello
I've got this error: "Sorry... Didn't find column 1 of the table", for RBAC Role Users and also Network Interface.
I've followed the procedure, but can't get metrics...

I precise that I have a Enterprise Agreement subscription.

Any idea ?
Thanks

CCO v5.0 does not work under any context. Attempted multiple independent accounts, subscriptions, directories for EA/sponsored/pay-as-you-go tenants. Global admin on tenant, subscription owner.

CCO v4.1 does not list all subscriptions on the account. It only loads subscriptions on the tenant that the account was created in. Ie. Guest accounts on a different tenant does not load the subscriptions, even with Guest accounts that are global admin/owner. Is this by design?