Direct Query : trick to add rows ?

[admin_xlsior]

Dear friends,

In Direct Query (as I know of), we cannot add our own rows as all is from the "live" data source, and we cannot add table as well, unless the table is derived or manipulated from existing data.

Now I have an issue with my Status Order, like a common status, it would be something like : Ordered, In Stock, In Production, In Transit, Delivered, Invoiced. The issue is sometimes my data doesn't have all the statuses, and when it happened obviously the visual will only show whatever statuses we have. My expectation is, even when it's not exist, I can still show the statuses in my Visual. But as mentioned, for Direct Query, I cannot just create new table then input all my status over there (to make relation later)

I created a Group, when in my data have all those statuses, and put as below :

But turn out, when my data now don't have some of the status, the Group is not all showing, in the image we can see also some of the member values are slight dimmed/ blur, it is becase it is not exist at the moment.

Is there a trick that we can have those statuses in complete list ? or the only way is get the Status list from the data source ?

Thanks,

[Greg_Deckler]

@admin_xlsior Sounds like you need to create a composite model and perhaps use an Enter Data query to add the all of the statuses to that.

[admin_xlsior]

Yeah, but too bad, as I'm gonna use this report in my ERP, and it is demand to use pure Direct Query, cannot choose composite.

Can something in the Power Query do something about this ? 

Thanks

[AlexisOlson · Accepted solution]

If you must use pure DirectQuery, then you need to have a DirectQuery source that has all the things you need. It can still be a separate table but without composite modeling, it has to come from the source.