Forum Discussion

mitchpowell's avatar
mitchpowell
Regular Visitor
2 years ago

SELECTCOLUMNS in calculated table - column order wrong

I have a strange issue. Using 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 SELECTCOLUMN...
Need Help
v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
2 years ago

Hi, mitchpowell 

 

Can you provide 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.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mitchpowell's avatar
mitchpowell
Regular Visitor
2 years ago

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 "Ext Price" is fourth from last but shows up as last column in results. 

 

Distribution Export =

VAR FilterTable =
    SELECTCOLUMNS (
        FILTER ( Invoice, [Invoice vs Distr Amt] = 0 ),
        "Key", Invoice[Key]
    )
VAR ReturnTable =
    SELECTCOLUMNS (
        //        CALCULATETABLE ( Distribution, FilterTable ),
        CALCULATETABLE (
            Distribution,
            FilterTable,
            FILTER ( Distribution, Distribution[Debit Entity] <> "" ),
            'Conversion entities'
        ),
        "Branch", Distribution[Debit Entity],
        "Type", If(Distribution[Amount]<0,"Credit Memo", "Invoice"),
        "Invoice", RELATED ( Invoice[Invoice] ),
        "Document Date",
            IF (
                RELATED ( Invoice[Status_Date] ) < DATE ( 2016, 1, 1 ),
                DATE ( 2016, 1, 1 ),
                RELATED ( Invoice[Status_Date] )
            ),
        "Post Period",
            FORMAT (
                IF (
                    RELATED ( Invoice[Status_Date] ) < DATE ( 2016, 1, 1 ),
                    DATE ( 2016, 1, 1 ),
                    RELATED ( Invoice[Status_Date] )
                ),
                "MM-YYYY"
            ),
        "Customer ID", RELATED ( Invoice[F_Customer] ),
        "Location", "MAIN",
        "Terms", "30D",
        "AR Account", Distribution[Debit Account],
        "AR Subaccount", Distribution[Debit Subaccount],
        "Project", Distribution[Job],
        "Balance", Distribution[Amount],
        "Amount", Distribution[Amount],
        "Line Branch", Distribution[Debit Entity],
        "Ext Price", Distribution[Amount],
        "Account", Distribution[Debit Account],
        "Subaccount", Distribution[Credit Subaccount],
        "temp_Folder", Distribution[Folder]
    )
RETURN
    ReturnTable