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

How about this crude approach?

 

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

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