Executing a SQL statement from PowerBI

[JGARROD]

HI, 

I am wondeirng if anyone has been able to execute multiple SQL statments from PowerBI. I have a table which is being created in SQL Server which contain basically a profiling of a number of other tbales. Within this table there is a column which contains a SQL statement that filters down to teh population we are searching. e.g. we are looking at a certain column in a table to check if character lenght is less tha 30, we are retuning how may rows meet this or fail this, this is then recorded in the table along with the SQL used to show the population that fail the rule. 

here is an example of the results table with the SQL code that i would want to execute in PowerBI in order to show the detail for those 9 fails.:

Table	Linked table	Rule	Number of fails	SQL
Person	Person_Title	< 30 characters	9	SELECT * FROM person WHERE LEN(PERSON_Title) > 30

Is this possible or do i just need to do all the profiling in PowerBI itself and skip the SQL server?

Thanks

[Daryl-Lynch-Bzy]

Hi @JGARROD - you can take advantage of the Table.ColumnNames - PowerQuery M | Microsoft Docs in an add custom column in Power Query.  The column names can be expanded to new rows and then you can add the necessary test.  Please refer to the following example:

let
    Source = Sql.Databases("localhost"),
    AdventureWorksDW1 = Source{[Name="AdventureWorksDW2019"]}[Data],
    #"Filtered Rows" = Table.SelectRows(AdventureWorksDW1, each ([Kind] = "Table")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Name", "Table Name"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Column Name", each Table.ColumnNames( [Data] )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data", "Schema", "Item", "Kind"}),
    #"Expanded Column Name" = Table.ExpandListColumn(#"Removed Columns", "Column Name"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column Name",{{"Column Name", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Test Column Name Length", each Text.Length([Column Name]) <= 30, type logical)
in
    #"Added Custom1"