CODEWITHSUNDEEP
powerbipowerbi-desktop
dougp
dougp

Reputation: 3089

Power BI: Renaming many tables and columns using the keyboard

Is there a way to use the keyboard to rename tables and columns in Power BI. I have hundreds (or thousands) of columns and tables whose names need to be more human-readable than what is in the database. Using right-click | rename is very slow. Tabbing to the column and hitting F2 doesn't appear to work. What is the keystroke to enter rename mode?

Or... Is there a way to open a .pbix file in a text editor so I can do the work there? (Certainly Microsoft must have chosen some open, standard, portable format for the file -- like XML? ;) ) I have unzipped the file, but the DataModel file appears to be a binary and not an archive.

Upvotes: 1

Views: 2001

Answers (2)

dougp
dougp

Reputation: 3089

Based on user12439754's answer...

(The "ease of use" for this task within Power BI is horrible.)

Since I'm using SQL Server, I was able to write a script that does much of the work.

Issues/future enhancements:

  • Parameterize the schema (or search for all of them).
  • Remove the comma at the end of the #"Renamed Columns" definition.

Usage:

  • Run the script.
  • Remove the comma at the end of #"Renamed Columns".
  • Move column names to #"Removed Columns" as needed.
  • Change the names to what you want the users to see. Paste the result (one table at a time) into the advanced editor.
declare @q table (
    id int identity(1,1) not null,
    tbl varchar(128) not null,
    col varchar(128) not null
)

insert @q

select o.name as 'Table'
, c.name as 'Column'

from sys.sysobjects o
  inner join sys.syscolumns c on c.id = o.id
  inner join sys.schemas s on s.schema_id = o.uid

where s.name = 'dbo'

order by o.name
, c.colorder


declare @tbl varchar(128), @t varchar(128), @c varchar(128)
select @tbl = (select top 1 tbl from @q order by id)
declare @i int, @max int
set @i = 1
select @max = count(*) from @q

declare @out table(
    id int identity(1,1) not null, 
    a varchar(4000) not null
)


while @i <= @max
begin
    select @t = (select tbl from @q where id = @i)
    insert @out
    values ('let')
    , ('    Source = Sql.Database("FinancialDM", "FinancialDataMart"),')
    , ('    dbo_' + @t + ' = Source{[Schema="dbo",Item="' + @t + '"]}[Data],')
    , ('    #"Removed Columns" = Table.RemoveColumns(dbo_' + @t + ',{}),')
    , ('    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{')

    while @tbl = @t and @i <= @max
    begin
        select @c = '        {"' + col + '", "' + col + '"}, ' from @q where id = @i
        insert @out
        values (@c)
        set @i = @i + 1
        select @t = (select tbl from @q where id = @i)
    end

    insert @out
    values ('    })')
    , ('in')
    , ('    #"Renamed Columns"')
    , ('')
    , ('')
    , ('')

    set @tbl = @t
end

select *
from @out

Upvotes: 1

Spaghetti
Spaghetti

Reputation: 187

Sorry in advance if this only partially solves your problem-

One possible way to speed up the renaming of multiple columns within a table would be:

You will still need to change the table names manually - but this may speed up the column renaming especially if you have lots of tables with 20+ columns

  1. Open Power Query Editor
  2. Navigate to the table you wish to change the columns in (Rename it while your at it)
  3. Reorder a column
  4. Take the column names from the reorder columns step in the advanced editor
  5. Manipulate in Excel or a text editor of your choice (this may require some work the first time but you can create something that will generate the necessary output)
  6. Insert your string of changed column names in format: {"Column Original", "Column Changed"} to a newly inserted step #"Renamed Columns" = Table.RenameColumns(#"Last Step",{{"Column Original", "Column Changed"},{"Column Original", "Column Changed"},{"Column Original", "Column Changed"}}),

Upvotes: 0

Related Questions