Forum Discussion

JGARROD's avatar
JGARROD
Icon for Helper I rankHelper I
4 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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"