Archive for year: 2016

Scenario: You’d want to have user-friendly field names in Tabular, Power Pivot, and Power BI Desktop, such as Claim Amount as opposed to database column names, such as ClaimAmount or Claim_Amount. Multidimensional has a feature that automatically delimits words with spaces when it detects a title case or underscore but Tabular lacks this feature. While you can rename fields in Tabular on field at the time, each step requires a commit action, thus taking long time to rename all fields.

Solution: While I’m not aware of a tool for renaming fields, the following approach should minimize the tedious work on your part:

1. Wrap your table with a SQL view. It’s a good practice anyway.
2. Alias the table columns. If you have a lot of columns, the easiest way to alias your columns is to use vertical copy and paste.
   1. In SSMS, script the table as SELECT TO. This generates the SELECT statement in a suitable format for the next steps (column names enclosed in square brackets, comma on the left).
   2. Hold the Alt key and select all columns by doing a vertical selection to enclose all column names, excluding the commas.
      
   3. Press Ctrl-C to copy.
   4. Hold the Alt key again. Click a place to the right of the first column and drag the mouse cursor down until you reach the last row where the last column is. You should see a vertical line going down.
   5. Type ” AS ” without the quotes. The net effect is that SSMS enters AS for each column.
   6. Press Ctrl-V to paste the column names. Here is the net result:
      
3. Now you can delimit the words with spaces. But if you have many columns, this can quickly get tedious too. Enter regular expressions.
4. Hold the Alt key again for vertical selection and select all alias columns, excluding “AS”.
5. Press Ctlr-H to bring up the SSMS Find & Replace. In the Find field, enter the regular expression ~(\[)[A-Z]. This expression searches for any capital letter after the left square bracket [.
6. In the Replace field, enter ” \0″ without the quotes. Notice that these is a space before the backslash. This replaces the capital letter match with an empty space and the capital letter.
7. Check the “Match Case” and “Use Regular Expressions”. Make sure that the “Look In” drop-down is set to Selection to avoid apply the replace to all the text.
8. Click Replace All. Now you have all words delimited.
   

The regular expression I use is not perfect. It won’t discriminate capital letters; for example, it will delimit consecutive capital letters, such as ID as I D, but it’s faster to fix the exceptions than doing all replacements manually. And if you end up with a better expression, please send it my way. The last step, of course, is to import the view and not the table in Tabular, Power Pivot, or PBI Desktop.