SQL queries in Excel using M Power Query feature

You can write SQL queries directly in Excel using Microsoft Excel's Power Query feature. Power Query allows you to connect to various data sources, including databases, and then manipulate and transform data using SQL-like queries. Here's how you can do it:

Imagine we have two reports which connected to clickhouse database table with Power Query

Query1 = Odbc.Query("dsn=ClickHouse", "

select number row_number, floor(randNormal(10, 2),1) value

from numbers(6)

")

Query2 = Odbc.Query("dsn=ClickHouse", "

select toDate('2023-01-01') + number as day_date,floor(randNormal(10, 2),1) value 

from numbers(120)

")

But as you can notice, it's very inconvenient to change SQL and perform further analysis. To make working with SQL and changing it right in an Excel workbook, let's create a table where we can put our code for a convenient way to work with it and apply changes immediately

Once you have created a table within your queries, navigate to Power Query and establish a connection to retrieve data from this table. Then, go to the advanced editor and populate it with the necessary code.

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"queries", type text}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

in

    #"Added Index"

Create a new blank connection and name it 'query' followed by the number of SQL queries in the previous table

let

    Source = Table.SelectRows(

    #"query table",

    each [Index] = 1),

    getQueryText  = Source{0}[queries],

    queryResult = Odbc.Query("dsn=ClickHouse", getQueryText )

in

    queryResult

Repeat that step each query you want. Finally we get our data model linked to table with sql. Lets make some changes in sql

Results: Immediately after pushing refresh, the changes from the SQL table are applied to the report