Enter a SQL Query by connecting to a .sql file instead of explicitly typing the SQL. This option would allow a developer the ability to maintain their SQL code in one place (instead of in Power Query and in the .sql file). Currently, it is possible to point to a .sql file and upload, but this requires manipulation of M, and changing the privacy settings to Public. Or via the use of calling the external stored procedure xp_cmdshell which requires database permissions to be granted, and the ability to write a batch script.
3 Comments
- Agree, need to link to the .Sql or whatever and have the Power BI. This way I can use the same query over and over and only store in one place for updating.
- For the record, anyone curious about the comment "it is possible with manipulation of M," here is one way it can be done: let SqlSource = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\user\ExtractQuery_ForPowerBI.sql"), null, null, 1252)}), #"Transposed Table" = Table.Transpose(SqlSource), #"Merged Columns" = Table.CombineColumns(#"Transposed Table",Table.ColumnNames(#"Transposed Table"),Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None),"Query"), Source = Sql.Database("server_name_here", "database_name_here", [Query=#"Merged Columns"{0}[Query], CreateNavigationProperties=false]) in Source This works by transposing the rows (since reading a file source brings a well formatted query in as multiple rows), merges them with a linebreak as the delimiter (to avoid issues with comments ruining the code when all on one line), then uses that value as the query source.
- Status added:Needs Votes
