If you have been using dataflows, and you have the return value of a query as something which is not in the form of a table, such as a record, list, or value. then you have seen that the dataflow automatically converts them all to tables. There is an explanation about it, and a method around it. In this article, I am explaining that.
Dataflow
If you haven’t heard about dataflow, or you have no experience with it, you can read about it here. a brief explanation is that dataflow is the Power Query process that runs in the cloud independent from the Power BI dataset and stores the data in a data storage technology.
Record, List, and Value
I have written previously about some of the data structures in Power Query, which you can read about it here. In addition to the table structure which is easy to understand, we also have the record, list, and value.
What if your Query in the Dataflow Returns List, Record, or Value?
One of the differences in the Power Query in the dataflow against the Power Query in the Power BI Desktop or Excel, is the way the the output of queries are treated.
If your query returns a result which is not a table or a function, then the dataflow automatically at the time of save & close will convert it to a table.
The screenshot above shows six queries in a dataflow, three of those are tables (Customers, Order_Details, and Order Grouped), and three of them are not (list sample, record sample, and value sample).
If I save and close this dataflow, I’ll get them all as below;
As you can see, the dataflow, at the time of saving and closing my queries, converted them all to tables. if I go to Edit Entities, I see they are now all tables;
Why this is happening?
The reason for this happening is I believe in the nature of dataflow queries that are writing into a destination. if we are talking about Power BI dataflows, that will be into Azure data lake storage as CSV files, so it makes sense to convert them all to table format before saving them as CSV file.
However, sometimes, you do need the query to return record, list, or value. For example, you might use that as the input of a function or another query. The good news is that you can, there is a little trick you should do.
Uncheck Enable Load
Every query that is marked as “enable load” will be saved into CSV format, unless it is a function. Usually, when you have a query with the output of record, list, or record, you don’t want to store that query itself. Instead, you want to use it in other queries as a source. In that case, you can simply uncheck the “enable load” option for that query.
Using this simple trick, you can have your queries intact (without extra transformations added by the dataflow) and returning the output you want.
This, of course, means that you cannot have the output of that entity saved, which means you won’t have a computed entity on it. However, most of the cases, the output of these queries are for other queries to use, not for a direct save into the destination.
Video
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.