# How to remove first n characters of headers in Power Query?



## cr731 (Aug 26, 2016)

I'm using Power Query to extract data from a SSAS database.  Each time PQ adds a column it names it as "Dimension.<Label>"  ... for example, Dimension.Customer, Dimension.Product.  I don't want the "Dimension." part - just the "Customer" or "Product" part.

I can't find a way to disable this behavior so I'm wondering what the best way to removing all characters up to the period "." for each column is?  Preferably not manually.

Thanks


----------



## cr731 (Aug 26, 2016)

I suspect Table.TransformColumnNames is the function I want... but I cannot wrap my head around what the nameGenerator function part is looking for.

https://msdn.microsoft.com/en-us/library/mt674878.aspx


----------



## GorD (Aug 26, 2016)

Probably not going to help much here  but here goes with a suggestion.

Is it possible to bring in the data without specifying a header  row?

if so then replace dimension. with null and then promote headers.

i guess im over simplifying things


----------



## Ozeroth (Aug 27, 2016)

Hi cr731,

A couple of ways you could do it below.
You just need to come up with a function that does what you want to each column name, and provide that as the 2nd argument of Table.TransformColumnNames. Two possible functions in *red* below.

1. Remove the first 10 characters from each column name:

```
= Table.TransformColumnNames(PreviousStep, [COLOR=#ff0000][B]each Text.RemoveRange(_,0,10)[/B][/COLOR] )
```

2. Remove everything up to the first "." from each column name:

```
= Table.TransformColumnNames(PreviousStep , [COLOR=#ff0000][B]each Splitter.SplitTextByEachDelimiter({"."}, null, false)(_){1}[/B][/COLOR] )
```


----------



## cr731 (Aug 29, 2016)

Ozeroth said:


> Hi cr731,
> 
> 2. Remove everything up to the first "." from each column name:
> 
> ...



I'd like to use this approach; however, I get Expression.Error: There weren't enough elements in the enumeration to complete the operation.  Details: List.


----------



## billszysz (Aug 29, 2016)

Hi cr731,
I do not see your data but try this way

```
let
    PreviousStep = SomeTransformation,
    Headers = List.Transform(Table.ColumnNames(PreviousStep), each {_, Text.Replace(_,"Dimension.","")}),
    Renamed = Table.RenameColumns(PreviousStep, Headers)
in
    Renamed
```

Regards


----------



## cr731 (Aug 29, 2016)

billszysz said:


> Hi cr731,
> I do not see your data but try this way
> 
> ```
> ...



"Dimension." was just one example - I actually need to remove everything up to the first "."  (another one could be Time.Period, etc.)


----------



## billszysz (Aug 29, 2016)

No problem 

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Headers = List.Transform(Table.ColumnNames(Source), each {_, Splitter.SplitTextByPositions({Text.PositionOf(_,".",Occurrence.First)+1}) (_){0}}),
    Result = Table.RenameColumns(Source,Headers)
in
    Result
```


----------



## Ozeroth (Aug 29, 2016)

cr731 said:


> I'd like to use this approach; however, I get Expression.Error: There weren't enough elements in the enumeration to complete the operation.  Details: List.



Oh, you must have some column names that don't contain "." that don't need to be changed.

To handle a mixture of column names with/without "." use something like:


```
= Table.TransformColumnNames(PreviousStep, each if Text.Contains(_, ".") then Splitter.SplitTextByEachDelimiter({"."}, null, false)(_){1} else _)
```


----------



## cr731 (Aug 29, 2016)

Thanks for all the responses; I ended up with the following:


```
Table.TransformColumnNames(PreviousStep, each Text.End(_, Text.Length(_) - Text.PositionOf(_,".") - 1))
```

And yes, for some reason the Measures columns don't include a "." i.e. just "Revenue" or "LBs" not Measures.Revenue, etc.


----------

