Power Query M list all functions with = #shared

[DavidMoss]

I just saw the PASS BA Data Preparation in Power BI presentation by @Rad_Reza and i was really impressed with the #shared function.

Just try it out open a blank query and type    = #shared

and you will see a list of functions from which you can click the function and see a detailed description along with its parameters. Cool for finding new Power Query M functions.

Or if you prefer simply in advanced editor cut and paste over existing text the following

let
Source = #shared
in
Source

see more after time 39:26 here

https://www.youtube.com/watch?v=5QMfMpdCrb4

Hey guys....If you find this useful give it some kudos or a like.

[ml3z]

I tried this 

let
Source = #shared
in
Source

and get the following error

Feedback Type:
Frown (Error)

Error Message:
Index was outside the bounds of the array.

Stack Trace:
at Microsoft.Mashup.Client.UI.Shared.PageManager.GridPages.GetPageRow(Int32 pageRowIndex)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.<>c__DisplayClass235_0.<RenderRowHeaderPages>b__0(Int32 pageIndex)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.JsPageContainerWrapper.AppendPages(PageHtmlProvider pageHtmlProvider, IEnumerable`1 indexesOfPagesToAppend)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.JsPageContainerWrapper.UpdatePages(PageHtmlProvider pageHtmlProvider, IEnumerable`1 indexesOfPagesToRemove, IEnumerable`1 indexesOfPagesToAppend, IEnumerable`1 indexesOfPagesToPrepend)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.RenderRowHeaderPages(HtmlGridPageRenderer gridPageRenderer, GridPages gridPagesToRender, GridPageDiffs gridPageDiffs)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.RenderGrid()
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.<OnViewportResize>b__200_0(Int32 numberOfRowsRetrieved)
at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.<>c__DisplayClass242_1.<RetrieveRowsAsync>b__0(PreviewReference previewReference, IList`1 rows)
at Microsoft.Mashup.Client.UI.Shared.Models.Results.Record.RecordRowProvider.TryHandleGridResult(PreviewReference previewReference, FormulaResult formulaResult, Action`2 onSuccess)
at Microsoft.Mashup.Client.UI.Shared.Models.Results.GridRowProvider.OnEvaluationCompleted(PreviewReference previewReference, Action`2 onSuccess, Action`1 onException)
at Microsoft.Mashup.Client.UI.Shared.Models.Results.GridRowProvider.<>c__DisplayClass12_0.<GetRowsAsync>b__1(PreviewReference previewReference)
at Microsoft.Mashup.Host.Document.Evaluation.PreviewEvaluationQueue.<>c__DisplayClass48_0.<OnPreviewCompleted>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Index was outside the bounds of the array.

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.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
at Microsoft.Mashup.Client.UI.Windows.UIHost.RaiseErrorDialog(IWindowHandle activeWindow, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass15_0.<HandleException>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialogBase`1.DelayedInitializationExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass5_0.<Post>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.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.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.Application.ThreadContext.LocalModalMessageLoop(Form form)
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.Application.RunDialog(Form form)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>c__DisplayClass57_0.<ShowModal>b__0()
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.ShowModal(IWindowHandle owner)
at Microsoft.Mashup.Client.Excel.QueriesEditorWindow.ShowModal(IUIHost uiHost, FormulaHistoryManager formulaHistoryManager, ApplicationTracingHost applicationTracingHost, ExcelServices excelServices, ConnectionManager connectionManager, DialogManager dialogManager, FillManager fillManager, PackageManager packageManager, IFileDialogManager fileDialogManager, ITelemetryService telemetryService, IWindowHandle ownerWindow, IWorkbookIdentity workbookIdentity, QueriesEditorParameters queriesEditorParameters, Queries queries)
at Microsoft.Mashup.Client.Excel.UI.LegacyMashupClientUIServices.ShowQueriesEditorWindow(IUIHost uiHost, FormulaHistoryManager formulaHistoryManager, ApplicationTracingHost applicationTracingHost, ExcelServices excelServices, ConnectionManager connectionManager, DialogManager dialogManager, FillManager fillManager, PackageManager packageManager, IFileDialogManager fileDialogManager, ITelemetryService telemetryService, IWindowHandle ownerWindow, IWorkbookIdentity workbookIdentity, QueriesEditorParameters queriesEditorParameters, Queries queries)
at Microsoft.Mashup.Client.Excel.QueriesEditorWindowManager.ShowModalQueriesEditorWindow(IWindowHandle ownerWindow, IWorkbookIdentity workbookIdentity, QueriesEditorParameters queriesEditorParameters, Queries queries, IUndoScope undoScope)
at Microsoft.Mashup.Client.Excel.QueriesEditorWindowManager.<>c__DisplayClass13_1.<ShowModalQueriesEditorWindow>b__1(IUndoScope undoScope)
at Microsoft.Mashup.Client.Excel.Shim.NativeUndoServices.<>c__DisplayClass6_0.<InvokeUndoableAction>b__0()
at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
at Microsoft.Mashup.Client.Excel.Shim.NativeUndoServices.InvokeUndoableAction(IWorkbook workbook, UndoableActionType actionType, Action`1 action)
at Microsoft.Mashup.Client.Excel.QueriesEditorWindowManager.<>c__DisplayClass13_0.<ShowModalQueriesEditorWindow>b__0()
at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.NotifyGetDataPresence(IWorkbook workbook, Action action)
at Microsoft.Mashup.Client.Excel.QueriesEditorWindowManager.ShowModalQueriesEditorWindow(IWindowHandle ownerWindow, IWorkbookIdentity workbookIdentity, QueriesEditorParameters queriesEditorParameters, Queries queries)
at Microsoft.Mashup.Client.Excel.DialogManager.AddExcelTable(IWindowHandle ownerWindow, IWorkbook workbook, String tableName, String newQueryName)
at Microsoft.Mashup.Client.Excel.DialogManager.AddExcelTable(IWindowContext windowContext, IWorkbook workbook)
at Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.<>c__DisplayClass23_1.<OnAddExcelTableAction>b__1(IUndoScope undoScope)
at Microsoft.Mashup.Client.Excel.Shim.NativeUndoServices.<>c__DisplayClass6_0.<InvokeUndoableAction>b__0()
at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
at Microsoft.Mashup.Client.Excel.Shim.NativeUndoServices.InvokeUndoableAction(IWorkbook workbook, UndoableActionType actionType, Action`1 action)
at Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.<>c__DisplayClass23_0.<OnAddExcelTableAction>b__0(IWorkbook workbook)
at Microsoft.Mashup.Client.Excel.Shim.IWindowContextExtensions.TryInvokeOnParentWorkbook(IWindowContext windowContext, Action`1 action)
at Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.OnAddExcelTableAction(IWindowContext windowContext)
at Microsoft.Mashup.Client.Excel.NativeRibbonHandler.<>c__DisplayClass6_0.<OnRibbonButtonAction>b__0()
at Microsoft.Mashup.Client.Excel.NativeRibbonHandler.<>c__DisplayClass17_0.<InvokeRibbonActionAndHandleExceptions>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.Mashup.Client.Excel.NativeRibbonHandler.InvokeRibbonActionAndHandleExceptions(Action action)
at Microsoft.Mashup.Client.Excel.NativeRibbonHandler.OnRibbonButtonAction(IntPtr uiFrame, Int32 buttonID)
at Microsoft.Mashup.Client.Excel.NativeRibbonCallbacks.<>c__DisplayClass3_0.<OnRibbonButtonActionCallback>b__0()
at Microsoft.Mashup.Client.Excel.Native.NativeExcelCallbackInvoker.InvokeAndReturnHResult(Action action)
at Microsoft.Mashup.Client.Excel.NativeRibbonCallbacks.OnRibbonButtonActionCallback(IntPtr uiFrame, Int32 buttonID)

Supports Premium Content:
True

Formulas:

section Section1;

shared EmpReport = let
Source = Excel.CurrentWorkbook(){[Name="TEmployee"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Year", Int64.Type}, {"Start Month", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Emp ID", Int64.Type}, {"Starting Salary", Int64.Type}, {"Current Salary", Int64.Type}}),
CalcSalary = Table.AddColumn(#"Changed Type", "Salary Increase", each [Current Salary]-[Starting Salary]),
#"Changed Type1" = Table.TransformColumnTypes(CalcSalary,{{"Salary Increase", Int64.Type}}),
GetName = Table.AddColumn(#"Changed Type1", "Full Name", each [First Name]&" "&[Last Name] ,type text),
GetLogin = Table.AddColumn(GetName, "Login", each [Last Name]&Text.From([Emp ID]), type text ),
GetDate = Table.AddColumn(GetLogin, "Start Date", each #date([Start Year],[Start Month],1)),
#"Changed Type2" = Table.TransformColumnTypes(GetDate,{{"Start Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Full Name", "Start Date", "Login", "Salary Increase"})
in
#"Removed Other Columns";

shared Query1 = let
Source = #shared
in
Source;

shared #"EmpReport (2)" = let
Source = Excel.CurrentWorkbook(){[Name="EmpReport"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Full Name", type text}, {"Start Date", type datetime}, {"Login", type text}, {"Salary Increase", Int64.Type}})
in
#"Changed Type";