Jared on 31 Mar 2017 00:33:54
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.
- Comments (2)
RE: Enter a SQL Query by connecting to a .sql file
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.
RE: Enter a SQL Query by connecting to a .sql file
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.