Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DavidMoss
Advocate V
Advocate V

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.

1 REPLY 1
ml3z
New Member

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

 

 

#shared issue.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors