Forum Discussion
SQL Statement to query a Power BI Dataflow
Hello everyone I've looked everywhere for an answer to this, I couldn't find a working example. I have a very large Power BI Dataflow set up, will all Sales history over 10 years. My report, tha...
You should be able to add a filter step in your query editor to select just 2021 data. I don't think the dataflow necessarily uses SQL but that shouldn't matter.
Your query will look something like this with the new step.
let Source = PowerBI.Dataflows([]), #"xxxxxxxxxxxxxxxx" = Source{[workspaceId="xxxxxxxxxxxxxxxx"]}[Data], #"yyyyyyyyyyyyyyyy" = #"xxxxxxxxxxxxxxxx"{[dataflowId="yyyyyyyyyyyyyyyy"]}[Data], #"Sales" = #"yyyyyyyyyyyyyyyy"{[entity="Sales"]}[Data], #"Filtered Rows" = Table.SelectRows(#"Sales", each [sales_date] >= #date(2021, 1, 1)) in #"Filtered Rows"
In order to run SQL statements, you need a database engine to run those statements.
A Dataflow is not storing data in a SQL database. You can see that when you eexport your Dataflow in a JSON file on tag "ppdf:outputFileFormat".
Dataflow Gen1 is using csv files.
Dataglow Gen2 is using Apache Parquet files.
If you can figure out a way to connect to the files generated by Gen2 Dataflows, You can run SQL statement on Parquet files by using Apache Spark SQL.