Define Column Order using SELECTCOLUMNS

[Rich_P]

I'm using SELECTCOLUMNS to project a subset of fields from my data model onto an Excel worksheet. The example uses the expression below to force the columns to appear in the order I want them displayed.

EVALUATE
SELECTCOLUMNS (
    'GES BB Invoice Data',
    "ESS Key", 'GES BB Invoice Data'[ESS_KEY],
    "Inv #", 'GES BB Invoice Data'[Inv Num],
    "Source", 'GES BB Invoice Data'[Source],
    "Inv Amt Claimed", 'GES BB Invoice Data'[Inv Claimed Amt],
    "Unclaimed Amt", 'GES BB Invoice Data'[UnclaimedDeniedAmt],
    "Reason", 'GES BB Invoice Data'[Reason],
    "Inv Status", 'GES BB Invoice Data'[Inv Status]
)

However, the result appears in a completely random order. 

Can anyone explain why? Or how to get the results I need?

Thanks,

Rich P

[RichardCalvertX]

This question is about column order but the accepted answer is about data (row) order. How can the answer be 'correct'?

[v-frfei-msft · Accepted solution]

Hi @Rich_P ,

As this case, the ORDER BY clause is part of the EVALUATE statement and the drillthrough expression just accepts a table expression. One possible work around would be to wrap your SELECTCOLUMNS in a TOPN. You could then either set a reasonable limit on the drillthrough to stop people from trying to extract too much data or you could just put a huge number for the "N" amount (eg. 2 billion) so that it just does sorting, but does not realistically limit the rows.

[v-frfei-msft]

Hi @Rich_P ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.