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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Top Kudoed Authors
Users online (3,917)