cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JGARROD
Helper I
Helper I

Executing a SQL statement from PowerBI

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.:

 

TableLinked tableRuleNumber of failsSQL 
PersonPerson_Title< 30 characters9SELECT * 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

1 REPLY 1
Daryl-Lynch-Bzy
Super User
Super User

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"

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors