incorrect syntax near "server name\instance" expecting '(' or select

Question

I am still trying to learn/teach myself sql. I am adjusting a SCOM dashboard but 1 query gives an error. I am not sure why. I asked my DBA and he said for me to ask google. I get incorrect syntax near "server name\instance" expecting '(' or select at sql.Database("OPSMGR2016DW\OPSDBDW" I tried but still cannot figure it out. the full statement is:

let
Source = Sql.Database("OPSMGR2016DW\OPSDBDW", "OperationsManagerDW", [Query="SELECT DISTINCT DB_NAME(dovs.database_id) DBName,#(lf)mf.physical_name PhysicalFileLocation,#(lf)dovs.logical_volume_name AS LogicalName,#(lf)dovs.volume_mount_point AS Drive,#(lf)CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB#(lf)FROM sys.master_files mf#(lf)CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs#(lf)Where DB_NAME(dovs.database_id) LIKE '%OperationsManager%'"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"DBName", "DB Name"}, {"PhysicalFileLocation", "Physical File Location"}, {"LogicalName", "Logical Name"}, {"FreeSpaceInMB", "Free Space (MB)"}})
in
#"Renamed Columns"

Please point me in the direction of what the proper format is.

I also get the same thing in the area of Table.RenameColumns(Source,{ of incorrect syntax near 'Source'. Expecting '(' or Select

Answer 1

The M query looks all right to me, I would suspect the #(If) part in the sql query. We must make sure the query works in SQL level. Could you explain what the #(If) intended to do ?

SELECT DISTINCT DB_NAME(dovs.database_id) DBName,#(lf)mf.physical_name PhysicalFileLocation,#(lf)dovs.logical_volume_name AS LogicalName,#(lf)dovs.volume_mount_point AS Drive,#(lf)CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB#(lf)FROM sys.master_files mf#(lf)CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs#(lf)Where DB_NAME(dovs.database_id) LIKE '%OperationsManager%'

Answer 2

I am sorry to not answer again. I did not get notified about a response. I was going for an end goal of trying to check all of our servers free space and query the scom database using powerbi. I ended up trashing this query and rewriting it and it came out differently, but it worked.
The query I used is let
Source = Sql.Database("OPSMGR2016DW\OPSDBDW", "OperationsManagerDW", [Query="select *, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime from (#(lf)#(lf)SELECT Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime, ROW_Number() Over (Partition by fullname order by datetime desc) rn#(lf)FROM#(lf)Perf.vPerfRaw pvpr #(lf)inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId #(lf)inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId #(lf)inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId #(lf)WHERE CounterName = '% Free Space') tmp#(lf)WHERE rn=1"]),
#"Sorted Rows" = Table.Sort(Source,{{"SampleValue", Order.Ascending}})

in
#"Sorted Rows"