Null Error - Export via Power Automate w/ Filters using Query against a data set; Curbal.com process

[Casey_Alderson]

Hi All, 

I'm using Curbal.com's process for exporting data from a report via power automate button and run a query against a dataset while using select operators to pull through filters. The report I'm working on has a lot of filters and some columns will rightly have blanks in some rows. This process works until I come across the below Null value error within the Select Operation step. Hoping someone can help with this problem since I'm so close to making this work. 

InvalidTemplate. The execution of template action 'TalentMatrixPlacement' failed: The evaluation of 'query' action 'where' expression '@concat('"', replace(item()?['Talent Matrix Placement'], ', ', '", "'), '"')' failed: 'The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#replace for usage details.'.

Simplified version of the DAX Query below as well as the Select Operation and the expression used to dynamically change filter within the DAX Query.

// DAX Query

DEFINE

VAR __DS0FilterTable = 

TREATAS({@{join(body('ServiceLine'), ', ')}}, 'PSC Talent Management'[Service Line])

VAR __DS0FilterTable2 = 

TREATAS({@{join(body('Position'), ', ')}}, 'PSC Talent Management'[Position])

VAR __DS0FilterTable3 = 

TREATAS(

{@{join(body('EmployeeName'), ', ')}},

'PSC Talent Management'[Employee]

)

VAR __DS0FilterTable4 = 

TREATAS(

{@{join(body('Profile'), ', ')}},

'PSC Talent Management'[Job Profile - Current]

)

VAR __DS0Core = 

CALCULATETABLE(

SUMMARIZE(

'PSC Talent Management',

'PSC Talent Management'[Employee],

'PSC Talent Management'[Employee ID],

'PSC Talent Management'[Job Profile - Current],

'PSC Talent Management'[Primary Location],

'PSC Talent Management'[Management Level],

'PSC Talent Management'[Service Line]

),

KEEPFILTERS(__DS0FilterTable),

KEEPFILTERS(__DS0FilterTable2),

KEEPFILTERS(__DS0FilterTable3),

KEEPFILTERS(__DS0FilterTable4),

)

EVALUATE

__DS0Core

ORDER BY

'PSC Talent Management'[Employee],

'PSC Talent Management'[Employee ID],

'PSC Talent Management'[Job Profile - Current],

'PSC Talent Management'[Primary Location],

'PSC Talent Management'[Management Level],

'PSC Talent Management'[Service Line]

Select Operation - for each filter:  concat('"', replace(item()?['Data Name'], ', ', '", "'), '"') 

The expression with Join(Body( that you see within the Dax Query is using that select operator. As I mentioned the error is occuring within the Select Operation because it expects a string and not null but sometimes those filters will need to pull through the null option.

Thanks, 

Casey

[lbendlin · Accepted solution]

If any of the fields handed over from the Power BI integration are blank then you need to drop the related filtertable from the DAX.

Note that you shouldn't use CALCULATE(SUMMARIZE() - use SUMMARIZECOLUMNS instead.  Also don't use the ORDER BY unless you really really need to.

[Casey_Alderson]

Hi @lbendlin, 

Can you explain how you would rewrite the DAX using SUMMARIZECOLUMNS? I've only gotten this far by copying what others have done via YouTube. I need all of the filters to stay dynamic and the way she accomplished that is through the combo of TREATAS and KEEPFILTER within the larger CALCULATETABLE function.