Force CALCULATETABLE to always give back all columns even if it is blank

[NBEN66]

Hi all, 

I have very specific use case, where I run a DAX query on dataset with powerautomate. After I have the result I convert it to csv and send it out as report. The problem is that this connector has limitations and because of that i had to split up the DAX query to run on chunks of the dataset. Because of that I don't always get back a value for a column in run and when a I convert it to csv the columns move left and right. Is there a way to get back data even it is blank as 0 or "" to force the csv conversion to inlcude a blank value as well. 

Here are is sample Dax query and screenshots to better understand the issue:
Dax query:

DEFINE
var filteredTable = FILTER( 'SAP Open Order Lines', 'SAP Open Order Lines'[brand_label_customer] = 0 && 'SAP Open Order Lines'[Index] >=1 && 'SAP Open Order Lines'[Index] <=5000 && 'SAP Open Order Lines'[sales_organization_code] IN {"4552"} && 'SAP Open Order Lines'[sold_to_number] IN {1184557})

VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(filteredTable,'SAP Open Order Lines'[aprc_desc],'SAP Open Order Lines'[billing_date],'SAP Open Order Lines'[catalog_number],'SAP Open Order Lines'[confirmed_qty],'SAP Open Order Lines'[confirmed_qty_status],'SAP Open Order Lines'[current_customer_request_pgi_date],'SAP Open Order Lines'[current_promised_pgi_date],'SAP Open Order Lines'[customer_material_number],'SAP Open Order Lines'[customer_po_item_number],'SAP Open Order Lines'[customer_po_number],'SAP Open Order Lines'[delivered_qty],'SAP Open Order Lines'[delivery_number],'SAP Open Order Lines'[document_currency_code],'SAP Open Order Lines'[invoiced_qty],'SAP Open Order Lines'[item_create_date],'SAP Open Order Lines'[item_extended_net_value],'SAP Open Order Lines'[material_desc],'SAP Open Order Lines'[material_number],'SAP Open Order Lines'[new_current_customer_requested_pgi_date1],'SAP Open Order Lines'[order_status],'SAP Open Order Lines'[sales_document_item_number],'SAP Open Order Lines'[sales_document_number],'SAP Open Order Lines'[sales_organization_code],'SAP Open Order Lines'[ship_to_name],'SAP Open Order Lines'[ship_to_number],'SAP Open Order Lines'[shipped_qty],'SAP Open Order Lines'[sold_to_country],'SAP Open Order Lines'[sold_to_name],'SAP Open Order Lines'[sold_to_number]))

EVALUATE
__DS0Core

Couple of note to the query, all filters othern than the index one are fullly dynamic users can set up their desired report with custom filters witch are genarated in power automate than pasted in to the query this same goes for the Columns as well so they are always changing. 

This is how it looks like on Power Automate:

Here is 2 exmaple from the same query but on different chunks:

Frist result

Second result

As you can see the frist one does not have the billing_date but the second one has it. 

So when this get converted to CSV the frist query result move a whole column to the left. 

So to sum it up can I force the DAX query result to always display all of the columns that was requested even if they are blank ? 

I hope this is the right place to ask because this is mainly a Dax issue. 

Thank you for your help in advance

[lbendlin · Accepted solution]

Show advanced options. Include Nulls.

[NBEN66]

Hi, 

Thank you that is now bringing in all the values, cant belive I did not spot it this. But now I face a new issue. 

Frist tables row are still returning the filtered values without nulls:

But in the for result of the body all of the rows are there:

But I can only use first table rows to create csv, is there a way to use the "rows" from the output to generate a csv ? 

[lbendlin]

yes, you can grab any element of the response object if you specify it manually in the Create CSV step.