topic Re: Removing square brackets from column headers DAX query in Developer

Removing square brackets from column headers DAX query

mark_carlisle — Thu, 08 Oct 2020 05:17:50 GMT

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

Re: Removing square brackets from column headers DAX query

lbendlin — Thu, 08 Oct 2020 00:31:14 GMT

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?

Re: Removing square brackets from column headers DAX query

mark_carlisle — Thu, 08 Oct 2020 05:25:40 GMT

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.

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

Re: Removing square brackets from column headers DAX query

lbendlin — Thu, 08 Oct 2020 17:54:04 GMT

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?

Re: Removing square brackets from column headers DAX query

mark_carlisle — Thu, 08 Oct 2020 18:12:06 GMT

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

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.

Re: Removing square brackets from column headers DAX query

lbendlin — Thu, 08 Oct 2020 20:15:26 GMT

How about this crude approach?

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

Re: Removing square brackets from column headers DAX query

mark_carlisle — Fri, 09 Oct 2020 08:22:08 GMT

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.

Re: Removing square brackets from column headers DAX query

lbendlin — Fri, 09 Oct 2020 12:25:20 GMT

Sounds like you omitted -NoTypeInformation ?

Re: Removing square brackets from column headers DAX query

mark_carlisle — Fri, 09 Oct 2020 14:24:26 GMT

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

Re: Removing square brackets from column headers DAX query

lbendlin — Fri, 09 Oct 2020 14:33:57 GMT

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

Re: Removing square brackets from column headers DAX query

mark_carlisle — Fri, 09 Oct 2020 14:44:47 GMT

That's the one.

Thanks for the help.

Re: Removing square brackets from column headers DAX query

lbendlin — Fri, 09 Oct 2020 14:47:01 GMT

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

Re: Removing square brackets from column headers DAX query

Anonymous — Mon, 27 Sep 2021 23:06:19 GMT

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