VBA to change Data type of a Power Query loaded table

Question

I have tables loaded on my Excel from Power Query. Some columns do not retain the data type and format I have specified in power query. For Example: Percentage type changes to Decimal(87.82%>0.878236484) and date type changes to Number(1/1/2020>43831)

I hope to write VBA code to change the column data type by specifying the table field header.

Kindly help with this.

Thank you.

Answer 1

@Bubna, Shreya

If that's enough to run it from the sheet where table "ABC" exists, in a VBA module:

Option Explicit  
  
Sub FormatPercentColumn()  
    Dim TargetTable     As ListObject  
    Dim c               As ListColumn  
      
    Set TargetTable = ActiveSheet.ListObjects("ABC")  
    For Each c In TargetTable.ListColumns  
        If c.Name = "LR" Then  
            c.DataBodyRange.NumberFormat = "0.00%"  
            Exit For  
        End If  
    Next  
End Sub  

Answer 2

@Bubna, Shreya

Re. In my Excel Table called "ABC" I have a number of Percent Columns "GH%" , "AD%" etc. I want to write a code which would change the Data type of these Percent Columns which have "%" in their Column headers to type Percentage

Sub FormatPercentColumns()  
    Dim TargetTable     As ListObject  
    Dim c               As ListColumn  
      
    Set TargetTable = ActiveSheet.ListObjects("ABC")  
    For Each c In TargetTable.ListColumns  
        If InStr(1, c.Name, "%") Then _  
            c.DataBodyRange.NumberFormat = "0.00%"  
    Next  
End Sub  

Just in case: Office VBA Reference is avail. here & if you have other VBA issues please open seperate threads as this one becomes confusing for those who search the site - Thanks

Answer 3

Hello @Lz._ ,

Thank you for your help. By a table field header i mean my power query loaded loaded excel table has a name "ABC" and the Percentage Coulumn also has a fixed name "LR". Using these can i write VBA to change data type to Percentage.

Thank you.