Solved: Re: Removing square brackets from column headers D...

mark_carlisle · ‎10-07-2020

I'm attempting to streamline an extremely convoluted process developed by one of my colleagues that involves exporting data from a Power BI report, renaming various columns, then uploading the final CSV to an S3 Bucket. Ideally I want to do this programmatically so we can schedule it and forget about it. I'm able to export the data by using PowerShell, however, its not completely in the format I'd want. When I run the query the resulting CSV file contains [ ] around the column names. I've attempted to use SELECTCOLUMNS to rename the columns but this has the same result. Is there any way to achieve what I want in the DAX query?

Current DAX

EVALUATE
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] ) )
)

EDIT: DAX to remove invalid SELECTCOLUMNS

lbendlin · ‎10-09-2020

You're right. I see it too. How about this?

$Results | export-csv -Path $ExportPath -NoTypeInformation
(Get-Content -Path $ExportPath ) -replace '[\[\]]' | Set-Content -Path $ExportPath

View solution in original post

lbendlin · ‎10-07-2020

When I run such a query in DAX Studio I don't get any square brackets in the output format. How are you piping the results of the query into your output file?

mark_carlisle · ‎10-07-2020

Confirmed your test, the square brackets are not present in DAX Studio when running the same query. The full PowerShell script is below and is ran in PowerShell 7.

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

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

$Query = 
"EVALUATE
    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] ) )
)"

$ExportPath = "REDACTED\Export.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 | export-csv -Path $ExportPath -NoTypeInformation -Encoding UTF8noBOM
$Connection.Dispose()
$Connection.Close()

SOURCE: https://sqlitybi.com/how-to-export-data-from-power-bi-using-xmla-endpoints/

lbendlin · ‎10-08-2020

Hmm. The square brackets are kinda needed as they separate the column name from the table name. You cannot guarantee that the query only returns values from a single host table. What would you want to replace them with?

Also, what made you move from the ADOmd client to the OLEdb OLAP.8 client?

mark_carlisle · ‎10-08-2020

Ah yes so, as my code is above the table name precedes the column name in the square brackets. However, I've used SELECTCOLUMNS to get around this, so the script is now (first five columns as an example in the DAX query);

[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 Number`", 'XactlyTemplate'[Invoice Number1],
    `"Transaction Date`", 'XactlyTemplate'[Transaction Date],
    `"Customer Unique ID`", 'XactlyTemplate'[Customer Unique ID],
    `"Customer Name`", 'XactlyTemplate'[Customer Name]
)"

$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 | export-csv -Path $ExportPath -NoTypeInformation -Encoding UTF8noBOM
$Connection.Dispose()
$Connection.Close()

Which outputs;

"[Performance_Month]","[Invoice Number]","[Transaction Date]","[Customer Unique ID]","[Customer Name]"

So still retaining the square brackets. The aim would be to replace them with nothing.

As for the switch, that's because of the companies use of MFA but I intend to request a service account when I get this working, for now its a proof of concept so testing on my account with MFA is sufficient.

lbendlin · ‎10-08-2020

How about this crude approach?

$Results -replace '[\[\]]' | export-csv -Path $ExportPath -NoTypeInformation

mark_carlisle · ‎10-09-2020

Nice try unfortunately I just get a single column called Length with the number 19 in it which is the number of characters in the first column. Tried a few other regex combinations to remove just one or specify nothing as its replacement, same result. Figured it was going to need something hacky in PS to achieve.

lbendlin · ‎10-09-2020

Sounds like you omitted -NoTypeInformation ?

mark_carlisle · ‎10-09-2020

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()

lbendlin · ‎10-09-2020

You're right. I see it too. How about this?

$Results | export-csv -Path $ExportPath -NoTypeInformation
(Get-Content -Path $ExportPath ) -replace '[\[\]]' | Set-Content -Path $ExportPath

jimbow · ‎09-27-2021

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

mark_carlisle · ‎10-09-2020

That's the one.

Thanks for the help.

lbendlin · ‎10-09-2020

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

Removing square brackets from column headers DAX query

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024