I am using a scheduled Power Automate flow to query a Power BI dataset once per month and save the results as a CSV file. The past 12 months of CSV file data are loaded into the same Power BI report to have monthly snapshots with which I can show data trending.
The issue I’m having is that in the most recent CSV file that was created when the flow ran on the first of November, the order of the columns does not match the order in which they appear in my DAX query in Power Automate. I have several other CSV files that do have the correct column order, and I thought they were created by the same flow when I created and tested it a few weeks ago, but I honestly cannot be sure if that is the case or if I created them manually back around the time the flow was created.
Regardless of how/when the older CSV files were created, the issue I’m having is the mismatch in column order between the DAX query and the CSV file.
Is there any way to make sure the columns in the CSV file show up in the same order as they are in the DAX query?
I cannot share actual data, but here is a sanitized version of my DAX query:
DEFINE
VAR SnapshotData =
SELECTCOLUMNS(
FILTER(
'MyTable',
'MyTable'[Exclude] = FALSE() && 'MyTable'[WorkflowStatus] <> "Cancelled"
),
"Snapshot Month", MONTH(TODAY()) - 1,
"RowId", CONCATENATE(CONCATENATE(MONTH(TODAY()) - 1, "|"), [RowId]),
"VersionId", [VersionId],
"WorkflowNumber", [WorkflowNumber],
"WorkflowId", [WorkflowId],
"WorkflowStartedDate", [WorkflowStartedDate],
"WorkflowCompletionDate", [WorkflowCompletionDate],
"RiskAccepted", [RiskAccepted],
"RiskLevel", [RiskLevel],
"RiskRanking", [RiskRanking],
"WorkflowTargetDate", [WorkflowTargetDate],
"WorkflowStatus", [WorkflowStatus],
"SnapshotDate", TODAY(),
"LocationId", RELATED('Locations'[LocationId])
)
EVALUATE
SnapshotData
The order of the columns in the CSV files is:
[Snapshot Month]
[RowId]
[VersionId]
[RiskAccepted]
[RiskLevel]
[WorkflowStatus]
[SnapshotDate]
[LocationId]
[PHARiskRanking]
[WorkflowTargetDate]
[WorkflowNumber]
[WorkflowId]
[WorkflowStartedDate]
[WorkflowCompletionDate]