Removing square brackets from column headers DAX query

Here's a cleaner way, directly modifying the column names in the dataset, only having to write the file once.  The Split method treats the brackets as delimiters and selects the second element of the resulting array as the column name.

$Adapter.Fill($Results)
$Results.Columns.ColumnName | 
   ForEach-Object { 
      $Results.Columns[$_].ColumnName = $_.Split(@('[',']'))[1] 
   }
$Results | Export-CSV -Path $ExportPath -NoTypeInformation

No that was included code as it stands...

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") 

$PowerBIEndpoint = "REDACTED"
$PowerBILogin = ""
$PowerBIPassword = ""

$Query = 
"EVALUATE
SELECTCOLUMNS (
    SUMMARIZECOLUMNS (
        'XactlyTemplate'[Performance Month],
        'XactlyTemplate'[Invoice Number1],
        'XactlyTemplate'[Transaction Date],
        'XactlyTemplate'[Customer Unique ID],
        'XactlyTemplate'[Customer Name],
        'XactlyTemplate'[Customer State],
        'XactlyTemplate'[Customer Region],
        'XactlyTemplate'[ContractID],
        'XactlyTemplate'[Contract Start Date],
        'XactlyTemplate'[Contract End Date],
        'XactlyTemplate'[Order/Contract Term in Months],
        'XactlyTemplate'[Order/Contract Term in Days],
        'XactlyTemplate'[Usage Type],
        'XactlyTemplate'[AcctAssignGroup],
        'XactlyTemplate'[Revenue Type],
        'XactlyTemplate'[Transaction Type Description],
        'XactlyTemplate'[Stock_Material Code],
        'XactlyTemplate'[Stock_Material Code Description],
        'XactlyTemplate'[Product Name],
        'XactlyTemplate'[Sub-Product],
        'XactlyTemplate'[Recurring/Non-Recurring],
        'XactlyTemplate'[Currency],
        'XactlyTemplate'[Sales Org],
        'XactlyTemplate'[Business Unit],
        'XactlyTemplate'[Partner Account],
        'XactlyTemplate'[Partner Name],
        'XactlyTemplate'[Sales Team],
        'XactlyTemplate'[Employee_ID_2],
        'XactlyTemplate'[Employee_2_Split_Percentage],
        'XactlyTemplate'[Employee_ID_3],
        'XactlyTemplate'[Employee_3_Split_Percentage],
        'XactlyTemplate'[Employee_ID_4],
        'XactlyTemplate'[Employee_4_Split_Percentage],
        'XactlyTemplate'[Employee_ID_5],
        'XactlyTemplate'[Employee_5_Split_Percentage],
        'XactlyTemplate'[Employee_ID_6],
        'XactlyTemplate'[Employee_6_Split_Percentage],
        'XactlyTemplate'[Employee_ID_7],
        'XactlyTemplate'[Employee_7_Split_Percentage],
        'XactlyTemplate'[Employee_ID_8],
        'XactlyTemplate'[Employee_8_Split_Percentage],
        'XactlyTemplate'[Employee_ID_9],
        'XactlyTemplate'[Employee_9_Split_Percentage],
        'XactlyTemplate'[Employee_ID_10],
        'XactlyTemplate'[Employee_10_Split_Percentage],
        'XactlyTemplate'[Employee_ID_1],
        TREATAS ( { DATE ( 2020, 9, 1 ) }, 'XactlyTemplate'[Performance Month] ),
        `"No_of_Units`", CALCULATE ( SUM ( 'XactlyTemplate'[# of Units] ) ),
        `"Total_Amt`", CALCULATE ( SUM ( 'XactlyTemplate'[Total Amt] ) ),
        `"Employee_1_Split_Percentage`", CALCULATE ( SUM ( 'XactlyTemplate'[Employee_1_Split_Percentage] ) )
    ),
    `"Performance Month`", 'XactlyTemplate'[Performance Month],
    `"Invoice Number1`", 'XactlyTemplate'[Invoice Number1],
    `"Transaction Date`", 'XactlyTemplate'[Transaction Date],
    `"Customer Unique ID`", 'XactlyTemplate'[Customer Unique ID],
    `"Customer Name`", 'XactlyTemplate'[Customer Name],
    `"Customer State`", 'XactlyTemplate'[Customer State],
    `"Customer Region`", 'XactlyTemplate'[Customer Region],
    `"ContractID`", 'XactlyTemplate'[ContractID],
    `"Contract Start Date`", 'XactlyTemplate'[Contract Start Date],
    `"Contract End Date`", 'XactlyTemplate'[Contract End Date],
    `"Order/Contract Term in Months`", 'XactlyTemplate'[Order/Contract Term in Months],
    `"Order/Contract Term in Days`", 'XactlyTemplate'[Order/Contract Term in Days],
    `"Usage Type`", 'XactlyTemplate'[Usage Type],
    `"AcctAssignGroup`", 'XactlyTemplate'[AcctAssignGroup],
    `"Revenue Type`", 'XactlyTemplate'[Revenue Type],
    `"Transaction Type Description`", 'XactlyTemplate'[Transaction Type Description],
    `"Stock_Material Code`", 'XactlyTemplate'[Stock_Material Code],
    `"Stock_Material Code Description`", 'XactlyTemplate'[Stock_Material Code Description],
    `"Product Name`", 'XactlyTemplate'[Product Name],
    `"Sub-Product`", 'XactlyTemplate'[Sub-Product],
    `"Recurring/Non-Recurring`", 'XactlyTemplate'[Recurring/Non-Recurring],
    `"Currency`", 'XactlyTemplate'[Currency],
    `"Sales Org`", 'XactlyTemplate'[Sales Org],
    `"Business Unit`", 'XactlyTemplate'[Business Unit],
    `"Partner Account`", 'XactlyTemplate'[Partner Account],
    `"Partner Name`", 'XactlyTemplate'[Partner Name],
    `"Sales Team`", 'XactlyTemplate'[Sales Team],
    `"Employee_ID_2`", 'XactlyTemplate'[Employee_ID_2],
    `"Employee_2_Split_Percentage`", 'XactlyTemplate'[Employee_2_Split_Percentage],
    `"Employee_ID_3`", 'XactlyTemplate'[Employee_ID_3],
    `"Employee_3_Split_Percentage`", 'XactlyTemplate'[Employee_3_Split_Percentage],
    `"Employee_ID_4`", 'XactlyTemplate'[Employee_ID_4],
    `"Employee_4_Split_Percentage`", 'XactlyTemplate'[Employee_4_Split_Percentage],
    `"Employee_ID_5`", 'XactlyTemplate'[Employee_ID_5],
    `"Employee_5_Split_Percentage`", 'XactlyTemplate'[Employee_5_Split_Percentage],
    `"Employee_ID_6`", 'XactlyTemplate'[Employee_ID_6],
    `"Employee_6_Split_Percentage`", 'XactlyTemplate'[Employee_6_Split_Percentage],
    `"Employee_ID_7`", 'XactlyTemplate'[Employee_ID_7],
    `"Employee_7_Split_Percentage`", 'XactlyTemplate'[Employee_7_Split_Percentage],
    `"Employee_ID_8`", 'XactlyTemplate'[Employee_ID_8],
    `"Employee_8_Split_Percentage`", 'XactlyTemplate'[Employee_8_Split_Percentage],
    `"Employee_ID_9`", 'XactlyTemplate'[Employee_ID_9],
    `"Employee_9_Split_Percentage`", 'XactlyTemplate'[Employee_9_Split_Percentage],
    `"Employee_ID_10`", 'XactlyTemplate'[Employee_ID_10],
    `"Employee_10_Split_Percentage`", 'XactlyTemplate'[Employee_10_Split_Percentage],
    `"Employee_ID_1`", 'XactlyTemplate'[Employee_ID_1]
)"

$ExportPath = "REDACTED\DataExport.csv"

$Connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
$Results = New-Object System.Data.DataTable
$Connection.ConnectionString = "Provider=MSOLAP.8;Data Source="+ $PowerBIEndpoint +";UID="+ $PowerBILogin +";PWD="+ $PowerBIPassword  
$Connection.Open() 
$Adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $Query ,$Connection
$Adapter.Fill($Results) 
$Results -replace '[\[\]]' | export-csv -Path $ExportPath -NoTypeInformation #-Encoding UTF8noBOM -
$Connection.Dispose()
$Connection.Close()

That's the one.

Thanks for the help.

Please do let me know once you get the ADOmd to work. So far it errors out for me.