cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Bulk Renaming column in PQ with a pattern

@AlexisOlson  @ImkeF 

whenever I pull data from SSAS tables, they come as like this. 

smpa01_0-1642548290362.png

I want to bulk rename the colNames in a way that returns the value inside parenthesis

My end goal is this

smpa01_1-1642548365060.png

I tried this. Is it the best-optimized way to do this, I want the best optimization possible for a large tbl.

 

CT = Table.TransformColumnTypes(
    src,
    {{"db[colA]", Int64.Type}, {"db[colB]", Int64.Type}, {"db[colC]", Int64.Type}}
  ),
  Change = Table.TransformColumnNames(CT, each Text.Replace(Text.Split(_, "["){1}, "]", ""))

 

 

let
  src=Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlTSUTICYmOlWJ1oJRMgyxSIzZRiYwE=", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"db[colA]" = _t, #"db[colB]" = _t, #"db[colC]" = _t]
  ),
  CT = Table.TransformColumnTypes(
    src,
    {{"db[colA]", Int64.Type}, {"db[colB]", Int64.Type}, {"db[colC]", Int64.Type}}
  ),
  Change = Table.TransformColumnNames(CT, each Text.Replace(Text.Split(_, "["){1}, "]", ""))
in
  Change

 

Thank you in advance.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

I would try this way first, in a single rename columns step using List.Zip and List.Transform.

 

let
  src=Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlTSUTICYmOlWJ1oJRMgyxSIzZRiYwE=", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"db[colA]" = _t, #"db[colB]" = _t, #"db[colC]" = _t]
  ),
    #"Renamed Columns" = Table.RenameColumns(src,List.Zip({Table.ColumnNames(src), List.Transform(Table.ColumnNames(src), each Text.BetweenDelimiters(_, "[", "]"))}))
in
    #"Renamed Columns"

 

Pat

View solution in original post

Syndicate_Admin
Administrator
Administrator

I have a custom function ShortenColumnNames that I run on all of these:

(Source as table) as table =>
let
    ColNamesList = Table.ColumnNames(Source),
    Transform = Table.RenameColumns(Source,
        List.Transform(ColNamesList, each {_, Text.BetweenDelimiters(_, "[", "]")}))
in
    Transform

 So each of my SSAS queries starts like this (I keep the DAX queries separately):

let
    Source = AnalysisServices.Database(Workspace, Dataset, [Query=DAXQuery, Implementation="2.0"]),
    RenameCols = ShortenColumnNames(Source),
    [...etc...]

 

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

I would try this way first, in a single rename columns step using List.Zip and List.Transform.

 

let
  src=Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlTSUTICYmOlWJ1oJRMgyxSIzZRiYwE=", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"db[colA]" = _t, #"db[colB]" = _t, #"db[colC]" = _t]
  ),
    #"Renamed Columns" = Table.RenameColumns(src,List.Zip({Table.ColumnNames(src), List.Transform(Table.ColumnNames(src), each Text.BetweenDelimiters(_, "[", "]"))}))
in
    #"Renamed Columns"

 

Pat

Syndicate_Admin
Administrator
Administrator

I have a custom function ShortenColumnNames that I run on all of these:

(Source as table) as table =>
let
    ColNamesList = Table.ColumnNames(Source),
    Transform = Table.RenameColumns(Source,
        List.Transform(ColNamesList, each {_, Text.BetweenDelimiters(_, "[", "]")}))
in
    Transform

 So each of my SSAS queries starts like this (I keep the DAX queries separately):

let
    Source = AnalysisServices.Database(Workspace, Dataset, [Query=DAXQuery, Implementation="2.0"]),
    RenameCols = ShortenColumnNames(Source),
    [...etc...]

 

Syndicate_Admin
Administrator
Administrator

Thanks @mahoneypat  @AlexisOlson  

 

I tested out each of our Query plan

 

Me, @AlexisOlson and @mahoneypat  has exactly the same Query Plan as the following despite using different techniques. I don't know how?

smpa01_0-1642562810591.png

 

I will use this

Table.TransformColumnNames(src, each Text.BetweenDelimiters(_, "[", "]"))

 

They actually generate the same list of lists, but I would go with the one from @AlexisOlson too. That approach is more elegant.

 

Pat

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,345)