Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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"