{"id":943,"date":"2020-12-16T16:38:28","date_gmt":"2020-12-16T22:38:28","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=943"},"modified":"2020-12-21T13:32:48","modified_gmt":"2020-12-21T19:32:48","slug":"which-power-query-functions-exist-in-power-bi-but-not-excel","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2020\/12\/16\/which-power-query-functions-exist-in-power-bi-but-not-excel\/","title":{"rendered":"Which Power Query Functions exist in Power BI but not Excel?"},"content":{"rendered":"\n\n\n<h2>The final number of missing identifiers*<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"458\" height=\"237\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/12\/image-19.png\" alt=\"\" class=\"wp-image-958\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/12\/image-19.png 458w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/12\/image-19-300x155.png 300w\" sizes=\"(max-width: 458px) 100vw, 458px\" \/><figcaption>The only function that <strong>Power BI<\/strong> is missing is <code class=\"\" data-line=\"\">Facebook.Graph<\/code> (on my machine)<br><br>* This query checks for missing identifiers (which may be functions).<br>( You can filter by type if you convert <code class=\"\" data-line=\"\">#shared<\/code> to a table instead of calling  <code class=\"\" data-line=\"\">Record.FieldNames()<\/code> )<\/figcaption><\/figure>\n\n\n\n<h2>Generating the list using <code class=\"\" data-line=\"\">#shared<\/code><\/h2>\n\n\n\n<p>To get a list of all identifiers (functions, variables, constants) I use the variable named <a href=\"https:\/\/docs.microsoft.com\/en-us\/powerquery-m\/m-spec-sections#shared\">#shared<\/a> . Create a <code class=\"\" data-line=\"\">new blank query<\/code>, then paste this <\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-typescript\" data-line=\"\">let\n    IdentifierList = List.Sort(Record.FieldNames( #shared ))\nin\n    IdentifierList<\/code><\/pre>\n\n\n\n<p>I copy using <code class=\"\" data-line=\"\">Copy Entire List<\/code>, then storing the results into a PowerShell  variable. I Repeat the same with  Power  BI.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"317\" height=\"204\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/12\/image-18.png\" alt=\"\" class=\"wp-image-956\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/12\/image-18.png 317w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2020\/12\/image-18-300x193.png 300w\" sizes=\"(max-width: 317px) 100vw, 317px\" \/><figcaption>That&#8217;s more than I expected.<\/figcaption><\/figure>\n\n\n\n<p>To find out exactly which functions are different, use the Power Shell operator <code class=\"\" data-line=\"\">-NotIn<\/code><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-powershell\" data-line=\"\"># Find functions in PBI but not Excel\n$MissingExcel = $PowerBI | ? { $_  -notin $Excel }\n\n# Find functions in Excel but not PBI\n$MissingPowerBI = $Excel | ? { $_  -notin $PowerBI }<\/code><\/pre>\n\n\n\n<h2>The list of functions Power BI is Missing<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"\" data-line=\"\">Facebook.Graph<\/code><\/pre>\n\n\n\n<h2>The list of functions Excel is Missing<\/h2>\n\n\n\n<p>Note: This is the list for today, on my machine. Run the <code class=\"\" data-line=\"\">#shared<\/code> query to find any changes.<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"\" data-line=\"\">AI.ExecuteInProc\nAI.ExecuteInternal\nAI.ExternalSort\nAI.GetAutoMLEntity\nAI.SampleStratifiedWithHoldout\nAI.TestConnection\nAIFunctions.Capacities\nAIFunctions.Contents\nAIFunctions.ExecuteInternal\nAIFunctions.GetAutoMLEntity\nAIFunctions.PostProcess\nAIInsights.Contents\nAIInsights.ContentsGenerator\nAML.Execute\nAML.ExecuteBatch\nActerys.Contents\nActian.Contents\nAmazonRedshift.Database\nAnaplan.Contents\nApacheHiveLLAP.Database\nApacheSpark.Tables\nAsana.Tables\nAtScale.Cubes\nAutomationAnywhere.Feed\nAzureCostManagement.Contents\nAzureCostManagement.Tables\nAzureDataExplorer.Contents\nAzureDataExplorer.Databases\nAzureDevOpsServer.AccountContents\nAzureDevOpsServer.AnalyticsViews\nAzureDevOpsServer.Feed\nAzureDevOpsServer.Views\nAzureEnterprise.Contents\nAzureEnterprise.Tables\nAzureHiveLLAP.Database\nAzureMLFunctions.Contents\nAzureMLFunctions.Execute\nAzureMLFunctions.ExecuteBatch\nAzureSpark.Tables\nAzureTimeSeriesInsights.Contents\nBI360.Contents\nBIConnector.Contents\nBinary.Range\nCdm.Contents\nCdm.MapToEntity\nCds.Contents\nCds.Entities\nCherwell.SavedSearches\nCognite.Contents\nCommonDataService.Database\nCompression.Brotli\nCompression.LZ4\nCompression.None\nCompression.Snappy\nCompression.Zstandard\nCustomerInsights.Contents\nDataVirtuality.Database\nDataWorld.Contents\nDataWorld.Dataset\nDatabricks.Contents\nDenodo.Contents\nDocumentDB.Contents\nDremio.Databases\nDynamics365BusinessCentral.Contents\nDynamics365BusinessCentral.EnvironmentContents\nDynamics365BusinessCentralOnPremises.Contents\nDynamicsNav.Contents\nEmigo.Contents\nEmigo.GetExtractFunction\nEmigoDataSourceConnector.GetExtractFunction\nEmigoDataSourceConnector.NavigationFunctionType\nEntersoftBusinessSuite.Contents\nEssbase.Cubes\nExasol.Database\nFactSetAnalytics.AuthenticationCheck\nFactSetAnalytics.Functions\nFhir.Contents\nFoundry.Contents\nGeography.FromWellKnownText\nGeography.ToWellKnownText\nGeographyPoint.From\nGeometry.FromWellKnownText\nGeometry.ToWellKnownText\nGeometryPoint.From\nGithub.Contents\nGithub.PagedTable\nGithub.Tables\nGoogleAnalytics.Accounts\nGoogleBigQuery.Database\nGraph.Nodes\nHexagonSmartApi.ApplySelectList\nHexagonSmartApi.ApplyUnitsOfMeasure\nHexagonSmartApi.ExecuteParametricFilterOnFilterRecord\nHexagonSmartApi.ExecuteParametricFilterOnFilterUrl\nHexagonSmartApi.Feed\nHexagonSmartApi.GenerateParametricFilterByFilterSourceType\nHexagonSmartApi.GetODataMetadata\nHexagonSmartApi.Typecast\nHiveProtocol.HTTP\nHiveProtocol.Standard\nHiveProtocol.Type\nHtml.Table\nIRIS.Database\nImpala.Database\nIndexima.Database\nIndustrialAppStore.NavigationTable\nInformationGrid.Contents\nIntune.Contents\nJamfPro.Contents\nJethroODBC.Database\nKyligence.Database\nLinkar.Contents\nLinkedIn.SalesContracts\nLinkedIn.SalesContractsWithReportAccess\nLinkedIn.SalesNavigator\nLinkedIn.SalesNavigatorAnalytics\nLinkedIn.SalesNavigatorAnalyticsImpl\nList.ConformToPageReader\nMailChimp.Collection\nMailChimp.Instance\nMailChimp.Tables\nMailChimp.TablesV2\nMariaDB.Contents\nMarkLogicODBC.Contents\nMarketo.Activities\nMarketo.Leads\nMarketo.Tables\nMicroStrategyDataset.Contents\nMicroStrategyDataset.TestConnection\nMicrosoftAzureConsumptionInsights.Contents\nMicrosoftAzureConsumptionInsights.Tables\nMicrosoftAzureConsumptionInsights.Test\nMicrosoftGraphSecurity.Contents\nMixpanel.Contents\nMixpanel.Export\nMixpanel.FunnelById\nMixpanel.FunnelByName\nMixpanel.Funnels\nMixpanel.Segmentation\nMixpanel.Tables\nNetezza.Database\nPQ_Hi_World.Contents\nParquet.Document\nPaxata.Contents\nPdf.Tables\nPlanviewEnterprise.CallQueryService\nPlanviewEnterprise.Feed\nPlanviewProjectplace.Contents\nPowerBI.Dataflows\nPowerPlatform.Dataflows\nProductInsights.Contents\nProductInsights.QueryMetric\nProjectplace.Feed\nPython.Execute\nQubolePresto.Contents\nQuickBase.Contents\nQuickBooks.Query\nQuickBooks.Report\nQuickBooks.Tables\nQuickBooksOnline.Tables\nR.Execute\nRoamler.Contents\nShortcutsBI.Contents\nSiteimprove.Contents\nSmartsheet.Content\nSmartsheet.Query\nSmartsheet.Tables\nSnowflake.Databases\nSpark.Tables\nSparkPost.GetList\nSparkPost.GetTable\nSparkPost.NavTable\nSparkProtocol.Azure\nSparkProtocol.HTTP\nSparkProtocol.Standard\nSparkProtocol.Type\nSpigit.Contents\nStarburstPresto.Contents\nStripe.Contents\nStripe.Method\nStripe.Tables\nSurveyMonkey.Contents\nSweetIQ.Contents\nSweetIQ.Tables\nTable.AddFuzzyClusterColumn\nTable.CombineColumnsToRecord\nTable.ConformToPageReader\nTable.FuzzyGroup\nTeamDesk.Database\nTeamDesk.Select\nTeamDesk.SelectView\nTenforce.Contents\nTibcoTdv.DataSource\nTimeSeriesInsights.Contents\nTroux.CustomFeed\nTroux.Feed\nTroux.TestConnection\nTwilio.Contents\nTwilio.Tables\nTwilio.URL\nVSTS.AccountContents\nVSTS.AnalyticsViews\nVSTS.Contents\nVSTS.Feed\nVSTS.Views\nValue.Alternates\nValue.Expression\nValue.Lineage\nValue.Optimize\nValue.Traits\nVena.Contents\nVertica.Database\nVesselInsight.Contents\nWeb.BrowserContents\nWebtrends.KeyMetrics\nWebtrends.Profile\nWebtrends.ReportContents\nWebtrends.Tables\nWebtrendsAnalytics.Tables\nWitivio.Contents\nWorkforceDimensions.Contents\nWorkplaceAnalytics.Data\nZendesk.Collection\nZendesk.Tables<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The final number of missing identifiers* Generating the list using #shared To get a list of all identifiers (functions, variables, constants) I use the variable named #shared . Create a new blank query, then paste this I copy using Copy Entire List, then storing the results into a PowerShell variable. I Repeat the same with [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":951,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[48,13,9],"tags":[49,6,7,4],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/943"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=943"}],"version-history":[{"count":22,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/943\/revisions"}],"predecessor-version":[{"id":1001,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/943\/revisions\/1001"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/951"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=943"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=943"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=943"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}