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
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.