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

[cr731]

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]

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]

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]

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]

Hi cr731,

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

= Table.TransformColumnNames(PreviousStep , each Splitter.SplitTextByEachDelimiter({"."}, null, false)(_){1} )

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]

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]

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

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

 

[billszysz]

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]

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]

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.