Forum Discussion

DavidMoss's avatar
DavidMoss
Advocate V
10 years ago

Power Query M list all functions with = #shared

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.

Tips and Tricks

2 Replies

  • ml3z's avatar
    ml3z
    New Member
    4 years ago

    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";

     

     

  • scottostanek's avatar
    scottostanek
    Frequent Visitor
    9 months ago

    Clearly late to the post (first was 2016!) but I saw this method in a Youtube video and tried it. Works a treat but suggest adding a convert to table step, sorting on Name and renaming this object to 0_Query so it is first in the list and not mistaken. Any other active query objects will also be there but you could filter out Value = Table. YMMV

    let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Sorted Rows" = Table.Sort(#"Converted to Table",{{"Name", Order.Ascending}})
    in
    #"Sorted Rows"