I posted Linked Server answer, as netboyz, at https://community.powerbi.com/t5/Desktop/specifying-linked-server/td-p/294416 // Connection-Template: Microsoft.ACE.OLEDB.12.0 OLEDB; Provider=Microsoft.ACE.OLEDB.12.0; data source=FFFFFF; Mode=MMMMMM; // Connection-Template: MSOLEDBSQL OLEDB; Provider=MSOLEDBSQL; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE; // Connection-Template: SQLOLEDB OLEDB; Provider=SQLOLEDB; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE; // M-Template: OleDb_DataSource let Source = OleDb.DataSource("provider=PPPPPP;initial catalog=DBDBDB;data source=SSSSSS", [Query="QQQQQQ"]) in Source // M-Template: Excel_WorkbookPromoted let Source = Excel.Workbook(File.Contents("FFFFFF"),null,true), SheetImport=Source{[Name="TTTTTT"]}[Data], SheetImportPromoted=Table.PromoteHeaders(SheetImport, [PromoteAllScalars=true]) in SheetImportPromoted // M-Template: Sql_Database let Source = Sql.Database("SSSSSS", "DBDBDB", [Query="QQQQQQ"]) in Source // M-Template: Access_Database let Source = Access.Database(File.Contents("FFFFFF"), [CreateNavigationProperties=true]), ImportQuery = Source{[Schema="",Item="TQTQTQ"]}[Data] in ImportQuery // M-String Template-Based: Case1#OleDb_DataSource let Source = OleDb.DataSource("provider=MSOLEDBSQL;initial catalog=ExcelProto;data source=TRILITHIUM\SQLEXPRESS",[Query="EXEC dbo.spBuildPlanLS_Proto;"]) in Source // M-String Template-Based: PQJoin#Table_Join, using Excel.Workbook, to get and join Structured Tables let Source=Excel.Workbook(File.Contents("C:\PowerOpI\Merge\Merge.xlsm"), null, true), xlAlloc=Source{[Item="tblAlloc",Kind="Table"]}[Data], xlBOM=Source{[Item="tblBOM",Kind="Table"]}[Data], xlDates=Source{[Item="tblDates",Kind="Table"]}[Data], xlBuilds=Source{[Item="tblBuilds",Kind="Table"]}[Data], xlElements=Source{[Item="tblElements",Kind="Table"]}[Data], AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]), ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost]) in ABDBE // M-String: PQJoinLS_Proto, using OleDb.DataSource, with Linked Server Query, to get and join "Shadow Ranges" for Structured Tables let xlAlloc=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM LS_Proto...rngAlloc"]), xlBOM=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM LS_Proto...rngBOM"]), xlDates=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM LS_Proto...rngDates"]), xlBuilds=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM LS_Proto...rngBuilds"]), xlElements=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM LS_Proto...rngElements"]), AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]), ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost]) in ABDBE // M-String: Using OleDb.Query, with Linked Server Query, to get and join "Shadow Ranges" for Structured Tables let FileSource = "Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", xlAlloc = OleDb.Query(FileSource,"SELECT * FROM LS_Proto...rngAlloc"), xlBOM = OleDb.Query(FileSource,"SELECT * FROM LS_Proto...rngBOM"), xlDates = OleDb.Query(FileSource,"SELECT * FROM LS_Proto...rngDates"), xlBuilds = OleDb.Query(FileSource,"SELECT * FROM LS_Proto...rngBuilds"), xlElements = OleDb.Query(FileSource,"SELECT * FROM LS_Proto...rngElements"), AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]), ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost]) in ABDBE // M-String: PQJoinOPENROWSET using OleDb.DataSource, with OPENROWSET Query, to get and join "Shadow Ranges" for Structured Tables let xlAlloc=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngAlloc)"]), xlBOM=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngBOM)"]), xlDates=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngDates)"]), xlBuilds=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngBuilds)"]), xlElements=OleDb.DataSource("Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", [Query="SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngElements)"]), AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]), ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost]) in ABDBE // M-String: PQJoinOPENROWSET using OleDb.Query, with OPENROWSET Query, to get and join "Shadow Ranges" for Structured Tables let FileSource = "Provider=MSOLEDBSQL; initial catalog=ExcelProto; data source=TRILITHIUM\SQLEXPRESS", xlAlloc = OleDb.Query(FileSource,"SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngAlloc)"), xlBOM = OleDb.Query(FileSource,"SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngBOM)"), xlDates = OleDb.Query(FileSource,"SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngDates)"), xlBuilds = OleDb.Query(FileSource,"SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngBuilds)"), xlElements = OleDb.Query(FileSource,"SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\PowerOpI\Merge\Merge.xlsm;HDR=YES', rngElements)"), AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), ABDBE1=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), ABDBE2=Table.AddColumn(ABDBE1,"QtyExt", each [akQty]*[bmSubQty]), ABDBE=Table.AddColumn(ABDBE2,"CostExt", each [QtyExt]*[bmSubCost]) in ABDBE // Several ways: Get a local Workbook Source, Extract Tables from it, Extract Records Matching Criteria, Show Comments: let xlsmMerge=Excel.Workbook(File.Contents("C:\PowerOpI\Merge\Merge.xlsm"), null, true), // Select a source // Extract Tables from Workbook xlAlloc=xlsmMerge{[Item="tblAlloc",Kind="Table"]}[Data], // Select a Table within source; several Tables xlBOM=xlsmMerge{[Item="tblBOM",Kind="Table"]}[Data], xlDates=xlsmMerge{[Item="tblDates",Kind="Table"]}[Data], /* Find records matching criteria in one of the Tables */ test1=Table.SelectRows(xlAlloc, each [akTest]="Bringup"), // Select a subset of Table records matching criterion test2=Table.SelectRows(xlAlloc, (_) => [akTest]="Bringup"), // Several ways: each vs (_) test3=Table.SelectRows(xlAlloc, each [akSysID] & ":" & "RNIC-100" = [akEnetStructKey]), // Select using Structured-Key field // Select field akTest in record 2. "2" could be determined by a lookup or calc... test4=xlAlloc{2}[akTest], // Table{selectRecordinTable}[selectFieldinRecord] // Join some tables AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), // Join two Tables ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter) // Cascade Join, add another Table in test3 //Choose Variable to output from Query // Append / Union of tables let Source = Excel.Workbook(File.Contents("C:\PowerOpI\Merge\Merge.xlsm"), null, true), tblGroup1 = Source{[Item="tblGroup1",Kind="Table"]}[Data], tblGroup2 = Source{[Item="tblGroup2",Kind="Table"]}[Data], tblGroup3 = Source{[Item="tblGroup3",Kind="Table"]}[Data], tblUnion = Table.Combine({tblGroup1, tblGroup2, tblGroup3}) in tblUnion // PQCurrentWB Power Query, CurrentWorkbook, including tblPR let Source=Excel.CurrentWorkbook(), xlAlloc=Source{[Name="tblAlloc"]}[Content], // Name can reference a Structured Table name, or the name of a Named Range. xlBOM=Source{[Name="tblBOM"]}[Content], xlDates=Source{[Name="tblDates"]}[Content], xlBuilds=Source{[Name="tblBuilds"]}[Content], xlElements=Source{[Name="tblElements"]}[Content], xlPR=Source{[Name="tblPurchReq"]}[Content], AB=Table.Join(xlAlloc, {"akAllocRevKey"},xlBOM,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), ABDBE=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), ABDBEP=Table.Join(ABDBE,{"akPurchReq"},xlPR,{"prPurchReq"},JoinKind.LeftOuter), ABDBEPQ=Table.AddColumn(ABDBEP,"QtyExt", each [akQty]*[bmSubQty]), ABDBEPQC=Table.AddColumn(ABDBEPQ,"CostExt", each [QtyExt]*[bmSubCost]) in ABDBEPQC // Showing structure using CurrentWorkbook, transforming columns for calculation. let Source = Excel.CurrentWorkbook(), xlMenuRaw = Source{[Name="tblMenu"]}[Content], xlMenu = Table.TransformColumnTypes(xlMenuRaw,{{"Meal", type text}, {"Date", type date}, {"Time", type datetime}, {"MenuCourse", type text}, {"Quantity_Courses", type number}, {"Refrigerator", type text}, {"Prep", type text}}), xlIngredientsRaw = Source{[Name="tblIngredients"]}[Content], xlIngredients = Table.TransformColumnTypes(xlIngredientsRaw,{{"IngredCourse", type text}, {"Ingredients", type text}, {"Buy_Qty", type number}, {"AlreadyBought", type number}}), MenuIngredients=Table.Join(xlMenu, {"MenuCourse"},xlIngredients,{"IngredCourse"},JoinKind.LeftOuter), MenuIngredientsQtyA = Table.AddColumn(MenuIngredients,"QtyExt", each [Quantity_Courses] * [Buy_Qty]), MenuIngredientsQty = Table.TransformColumnTypes(MenuIngredientsQtyA,{{"QtyExt", type number}}) in MenuIngredientsQty // Using M in a direct Query in PowerBI Desktop - Need to cast variables to a numerical type for in-query calculations, note comments // PowerQuery, Excel Current WB, M, JOIN let Source = Excel.Workbook(File.Contents("C:\Users\Owner\Documents\PowerOpI\PowerQueryTool\ToolSource.xlsm"), null, true), xlAlloc=Source{[Item="tblAlloc", Kind="Table"]}[Data], xlBOM=Source{[Name="tblBOM", Kind="Table"]}[Data], xlDates=Source{[Name="tblDates", Kind="Table"]}[Data], xlBuilds=Source{[Name="tblBuilds", Kind="Table"]}[Data], xlElements=Source{[Name="tblElements", Kind="Table"]}[Data], xlPR=Source{[Name="tblPurchReq", Kind="Table"]}[Data], // PowerBI PowerQuery doesn't cast to type so it's important to do that "manually" to fields used for arithmetic. xlAlloc_transform=Table.TransformColumnTypes(xlAlloc,{{"akQty", Int64.Type}}), xlBOM_transform=Table.TransformColumnTypes(xlBOM,{{"bmSubQty", Int64.Type},{"bmSubCost", Currency.Type},{"bmExtCost", Currency.Type}}), AB=Table.Join(xlAlloc_transform, {"akAllocRevKey"},xlBOM_transform,{"bmBOMItemKeyIn"},JoinKind.LeftOuter), ABD=Table.Join(AB, {"bmBOMItemKeyOut"}, xlDates, {"daSchedItemRevKey"},JoinKind.LeftOuter), ABDB=Table.Join(ABD,{"daSchedItemRevKey"},xlBuilds,{"bdBuildRevKey"},JoinKind.LeftOuter), ABDBE=Table.Join(ABDB,{"bmBOMItemKey"},xlElements,{"emElementItemKey"},JoinKind.LeftOuter), ABDBEP=Table.Join(ABDBE,{"akPurchReq"},xlPR,{"prPurchReq"},JoinKind.LeftOuter), ABDBEPQ=Table.AddColumn(ABDBEP,"QtyExt", each [akQty]*[bmSubQty]), ABDBEPQC=Table.AddColumn(ABDBEPQ,"CostExt", each [QtyExt]*[bmSubCost]), // Again, for PowerBI PowerQuery, casting calculated fields to numerical type. ABDBEPQC_transform=Table.TransformColumnTypes(ABDBEPQC,{{"QtyExt", Int64.Type},{"CostExt", Currency.Type}}) in ABDBEPQC_transform