Forum Discussion

mark_carlisle's avatar
mark_carlisle
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's avatar
    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

lbendlin's avatar
lbendlin
Super User
5 years ago

Sounds like you omitted -NoTypeInformation ?

mark_carlisle's avatar
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()
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

     

  • lbendlin's avatar
    lbendlin
    Super User
    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's avatar
    mark_carlisle
    Advocate IV
    5 years ago

    That's the one.

     

    Thanks for the help.

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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