Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DavidMoss
Advocate V
Advocate V

Power Query M list all functions with = #shared

‎06-19-2016 05:15 AM

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.

Labels:
Message 1 of 2
9,375 Views
1 REPLY 1
ml3z
New Member

‎12-23-2021 05:20 PM

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

Message 2 of 2
3,363 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All