database schema

Visualize your SQL database schema within Power Query

Often, we need to look for a table or for a specific column in the database. Do you know it’s possible to create a simple query to visualize your database schema within Power Query ? Very few steps for great results.

How to display database schema (first method with data preview)

  • Add a blank query and name it ‘dbScheme’
  • Open the advanced editor
  • Copy this code and close advanced editor
let
    Source = Sql.Database( yourserver , yourdatabase ),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Schema", "Item", "Kind"})
in
    #"Removed Columns"

Click on a table and see a sample of content

Visualize your database schema within Power Query

How to display database schema (second method)

https://www.youtube.com/watch?v=icf7Hfj22vg

Here is the whole query :

let
    Source = Sql.Database("yourserver", "your database", [Query="
        SELECT 
            c.TABLE_NAME as 'Table', c.COLUMN_NAME as 'Column'
        FROM INFORMATION_SCHEMA.COLUMNS c
        INNER JOIN
            (SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            GROUP BY COLUMN_NAME
            HAVING COUNT(*) > 1
            ) dupes
        ON dupes.COLUMN_NAME = c.COLUMN_NAME
        
        ", CommandTimeout=#duration(0, 0, 1, 0)]),
    #"Sorted Rows" = Table.Sort(Source,{{"TABLE_NAME", Order.Ascending}})
in
    #"Sorted Rows"

How to (third method)

Phil Seamark method : Diagram your Database Tables using Power BI

See also how to document M functions : M functions documentation within Power Query

Leave a Reply

Your email address will not be published.