SELECTCOLUMNS in calculated table - column order wrong

Question

I have a strange issue. Using&nbsp;SELECTCOLUMNS to create a calculated table. One of the fields ALWAYS shows up as the LAST field in the resulting table REGARDLESS of where I place it in the&nbsp;SELECTCOLUMNS.&nbsp;&nbsp;What am I missing??

ahmedx · Answer

you should do this in power query there you need to add an index column to keep order

mitchpowell · Answer

There's a reason I'm doing this in desktop and not PQ so my question stands...

v-zhangti · Answer

Hi,&nbsp;mitchpowell&nbsp;
&nbsp;
Can you provide&nbsp;sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.
&nbsp;
Best Regards,
Community Support Team _Charlotte
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

mitchpowell · Answer

This is a calculated table. Code and screen shot of results below. Lot of code but the relevent part is that the end where the&nbsp;"Ext Price" is fourth from last but shows up as last column in results.&nbsp;&nbsp;Distribution Export =VAR FilterTable =&nbsp; &nbsp; SELECTCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; FILTER ( Invoice, [Invoice vs Distr Amt] = 0 ),&nbsp; &nbsp; &nbsp; &nbsp; "Key", Invoice[Key]&nbsp; &nbsp; )VAR ReturnTable =&nbsp; &nbsp; SELECTCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; // &nbsp; &nbsp; &nbsp; &nbsp;CALCULATETABLE ( Distribution, FilterTable ),&nbsp; &nbsp; &nbsp; &nbsp; CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Distribution,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FilterTable,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER ( Distribution, Distribution[Debit Entity] &lt;&gt; "" ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Conversion entities'&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Branch", Distribution[Debit Entity],&nbsp; &nbsp; &nbsp; &nbsp; "Type", If(Distribution[Amount]&lt;0,"Credit Memo", "Invoice"),&nbsp; &nbsp; &nbsp; &nbsp; "Invoice", RELATED ( Invoice[Invoice] ),&nbsp; &nbsp; &nbsp; &nbsp; "Document Date",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RELATED ( Invoice[Status_Date] ) &lt; DATE ( 2016, 1, 1 ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATE ( 2016, 1, 1 ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RELATED ( Invoice[Status_Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Post Period",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FORMAT (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RELATED ( Invoice[Status_Date] ) &lt; DATE ( 2016, 1, 1 ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATE ( 2016, 1, 1 ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RELATED ( Invoice[Status_Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "MM-YYYY"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "Customer ID", RELATED ( Invoice[F_Customer] ),&nbsp; &nbsp; &nbsp; &nbsp; "Location", "MAIN",&nbsp; &nbsp; &nbsp; &nbsp; "Terms", "30D",&nbsp; &nbsp; &nbsp; &nbsp; "AR Account", Distribution[Debit Account],&nbsp; &nbsp; &nbsp; &nbsp; "AR Subaccount", Distribution[Debit Subaccount],&nbsp; &nbsp; &nbsp; &nbsp; "Project", Distribution[Job],&nbsp; &nbsp; &nbsp; &nbsp; "Balance", Distribution[Amount],&nbsp; &nbsp; &nbsp; &nbsp; "Amount", Distribution[Amount],&nbsp; &nbsp; &nbsp; &nbsp; "Line Branch", Distribution[Debit Entity],&nbsp; &nbsp; &nbsp; &nbsp; "Ext Price", Distribution[Amount],&nbsp; &nbsp; &nbsp; &nbsp; "Account", Distribution[Debit Account],&nbsp; &nbsp; &nbsp; &nbsp; "Subaccount", Distribution[Credit Subaccount],&nbsp; &nbsp; &nbsp; &nbsp; "temp_Folder", Distribution[Folder]&nbsp; &nbsp; )RETURN&nbsp; &nbsp; ReturnTable&nbsp;&nbsp;

Forum Discussion

SELECTCOLUMNS in calculated table - column order wrong

4 Replies

Related content

SELECTCOLUMNS function

TMDL - calculated column

Calculated Table with UNION and SELECTCOLUMNS

Direct Lake Model - Calculated Column/Tables

Selectcolumns