Forum Discussion

Advocate IV

5 years ago

Solved

Removing square brackets from column headers DAX query

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 ...

Need Help

lbendlin
5 years ago
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

mark_carlisle

Advocate IV

5 years ago

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

Super User

5 years ago

How about this crude approach?

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

mark_carlisle

Advocate IV

5 years ago

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

Super User

5 years ago

Sounds like you omitted -NoTypeInformation ?

mark_carlisle

Advocate IV

5 years ago

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

Forum Discussion

Removing square brackets from column headers DAX query

Related content

Brackets in tablenames

Remove square Brackets from column headers DAX Query

Removing brackets and numbers included in brackets

DAX query results in brackets around column headers

Remove the column header in Table