Run a Query Against a Dataset, different number of input columns compared to output number of columns

[Domval]

I am running a run a query against a dataset step and I receive different number of columns in an output compared to number of columns I am giving as an input.

 

In picture for example I want to get "Signing Date" but this value magicaly dissapears in the output, however when I test this dax in actual PBi report it works as it should.

Any suggestions?

[wskinnermctc]

Was the column renamed? Like Sign Date to Signing Date?

Is the value blank or null?

[Domval]

Ok, when the output is blank the column will not appear but when theres a row with value its "back" again. I guess this can not be modified to have same number of columns even when the values are blank? Because I think the column differences is causing the problem with further steps of adding rows to a table.

Funny thing - other columns which appear to have blanks appears correctly as = ""... Maybe theres no difference in PBi view but will be in Power Query view on dataset level...

[wskinnermctc]

I don't really know what you are doing, but I have experienced similar things when dealing with blanks.

I think it depends on the data type and source.

I don't have precise examples, but there are times when columns don't show because a field is blank, sometimes they will show as "" and sometimes it will be null

Usually have to do some kind of empty() check or whatever to make sure my flow doesn't error.

[Domval · Accepted solution]

Basically when the field is blank it will show correctly in the run a query against a dataset step however when theres a field with null values it will not show but you cant replace null in power query with blank values with date format column, but theres no problem with text format columns so maybe writing a dax and formating the date column to text will help.

No it wont really because even text columns are missing... I guess manually replacing formats to all the selected columns to text format and replacing null values with blanks could solve the problem but its quite an impact for such little.

This dax format will eventually fix the problem (format to text and replace nulls with blanks): 

"Signing Date", FORMAT(COALESCE(AllMerge[Signing Date], BLANK()), "@"),

[hnguyen2323]

Hi @Domval ,

I'm struggling with the same issues for a flow I'm working on. When a column includes a null value, the whole row is skipped in the "Add a row into a table" step. The annoying part is if I just dump the data from the query run into a CSV file (instead of Excel file) the rows with null values in some columns show up just fine as empty space behind a comma.

There are other blank values in the dataset and they show up just fine on the report and I'm just wondering if they get pre-treated to turn into blanks unlike the fields I'm trying to grab. I don't own the dataset so I can just view and create reports off it.

I tried to the steps to format the cell to text and replace nulls with something else but that doesn't seem to work.

Have you found a solution?