Solved: Power Automate running a DAX query but creating a CSV with different sorting from original Power BI query

Power Automate running a DAX query but creating a CSV with different sorting from original Power BI query

I created the following 'Run a query against a dataset' instant cloud flow, where the output from such DAX query is transferred over to a CSV table, which is saved to a Sharepoint folder, and then the flow continues on until the contents of such CSV is parsed onto a Sharepoint list.

The flow is running fine, until I realized that after making a columns sorting criteria change to the data table in Power Query, the flow still produces a CSV with the same sorting criteria prior to those changes I had just made, as if it's "stuck in time" somehow.

To illustrate what I mean, I've put together a print where the left hand side brings the query directly out of a Power BI table (thus capturing the aforementioned changes in sorting criteria), and then after extracting the DAX code via Performance Analyzer and pasting onto the flow, I pasted the CSV resulted from such flow to the right hand side of the print (please note the query has an index column 'ID_Lanc', and the sorting criteria is performed before the insertion of such index column, which locks the order of sorting into place).

I even created a brand new flow after I changed the data table's sorting criteria in Power Query and saved the resulting CSV on a OneDrive folder, but the result produced is still the same: stuck in the old sorting criteria.

Upon investigation I'm pretty sure the culprit resides under the 'Run a query against a dataset' step, I just couldn't pinpoint exactly where by looking at the JSON code that comes out of it since I'm a newbie on all this.

Below is the DAX code placed under 'Run a query against a dataset'. I also tried running a simpler version of the code by getting rid of the '__DS0PrimaryWindowed'/TOPN piece and running only the '__DS0Core'/SUMMARIZE part of the code, and I also tried swapping SUMMARIZE with SUMMARIZECOLUMNS, but yet I keep getting the same CSV output with wrong (old) sorting criteria:

DEFINE
	VAR __DS0Core = 
		SUMMARIZE(
			'Fat_CC_Lancamentos_Orig',
			'Fat_CC_Lancamentos_Orig'[ID_Lanc],
			'Fat_CC_Lancamentos_Orig'[Banco/Cartão],
			'Fat_CC_Lancamentos_Orig'[Data Vencimento Fatura],
			'Fat_CC_Lancamentos_Orig'[Data],
			'Fat_CC_Lancamentos_Orig'[Descrição],
			'Fat_CC_Lancamentos_Orig'[Lançamento]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			100000,
			__DS0Core,
			'Fat_CC_Lancamentos_Orig'[ID_Lanc],
			1,
			'Fat_CC_Lancamentos_Orig'[Banco/Cartão],
			1,
			'Fat_CC_Lancamentos_Orig'[Data Vencimento Fatura],
			1,
			'Fat_CC_Lancamentos_Orig'[Data],
			1,
			'Fat_CC_Lancamentos_Orig'[Descrição],
			1,
			'Fat_CC_Lancamentos_Orig'[Lançamento],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'Fat_CC_Lancamentos_Orig'[ID_Lanc],
	'Fat_CC_Lancamentos_Orig'[Banco/Cartão],
	'Fat_CC_Lancamentos_Orig'[Data Vencimento Fatura],
	'Fat_CC_Lancamentos_Orig'[Data],
	'Fat_CC_Lancamentos_Orig'[Descrição],
	'Fat_CC_Lancamentos_Orig'[Lançamento]

I really ran out of ways of trying to solve this issue, any help on this one would be greatly appreciated!

Quick Links

Leaderboard