Column Order Different in CSV File Than in DAX Query

[DaveC]

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]

[EnterpriseDNA]

Hi @DaveC ,

Thank you for reaching out to the community.

While we wait for other members to share their insights, I’ve taken the liberty of using “Explain Simply,” one of the tools available within Data Mentor (you can explore it here: https://mentor.enterprisedna.co/explain-simply). It generated the following results:.

Feel free to checkout more of our Data Mentor features as you work on your report. These tools are designed to help with tasks like the one you’re working on, and they might accelerate your learning curve.

Cheers,

Enterprise DNA Support Team

[DaveC]

Found the solution! Sharing it here in case it can help others. The answer is to specify a custom column layout in the parameters of the Create CSV Table step of the Power Automate flow.

1. Select Custom under the Columns advanced parameter.
2. For each column, type the column name and the expression.
3. The expression should be item()?[‘[name of column as defined in DAX query]’]

[Keith]

I’m glad you found a solution.

Thanks for sharing your solution @DaveC

Happy solution
Keith