cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sheryl_1798_bb
New Member

Removal of repetitive words in the title

I am new to PBI, and I have a set of data with repetitive words in my title etc. 'abc - count of 123' is there a way to remove 'abc -' and leave 'count of 123'?

1 ACCEPTED SOLUTION

Hi @sheryl_1798_bb ,

 

Thanks for your clarification!

 

To be honest, I first thought you wanted to change the data and not the title. There are not that many tools to change column titles in Power Query. Still, this is what I found:

 

Before:

tomfox_0-1664126075107.png

 

 

After:

tomfox_1-1664126091976.png

 

There is no "find text and substitute/remove" function for titles in Power Query. But you could go into the M code of the advanced editor and do the copy and pasting there. I imagine that could save you a bit of time:

tomfox_2-1664126227133.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWJVjICsszALGMgyxzMMgGyLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Deaths - Alzheimer's disease and other dementias Sex: Both - Age: All Ages (Number)" = _t, #"Deaths - Mengitis disease and other dementias Sex: Both - Age: All Ages (Number)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Deaths - Alzheimer's disease and other dementias Sex: Both - Age: All Ages (Number)", Int64.Type}, {"Deaths - Mengitis disease and other dementias Sex: Both - Age: All Ages (Number)", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{
        {"Deaths - Alzheimer's disease and other dementias Sex: Both - Age: All Ages (Number)", "Alzheimer's disease and other dementias"},
        {"Deaths - Mengitis disease and other dementias Sex: Both - Age: All Ages (Number)", "Mengitis disease and other dementias"}
        })
in
    #"Renamed Columns"

 

As you can see, you can specify the renaming of many columns in just one steep. Another option could also be to change the name of the columns in your source system, i.e. by aliasing them accordingly in a view. 

 

Hope this helps! 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
sheryl_1798_bb
New Member

Hi @tackytechtom

 

Thanks for reaching out! 

sheryl_1798_bb_0-1664114058590.png

I want the titles to be 'Meningitis' and 'Alzheimer's disease and other dementias'. I have many columns like this in the dataset. So, Im looking for a way that helps me delete 'Deaths - ' and 'Sex: Both - Age: All Ages (Number)' from all the column titles at once.

 

Hi @sheryl_1798_bb ,

 

Thanks for your clarification!

 

To be honest, I first thought you wanted to change the data and not the title. There are not that many tools to change column titles in Power Query. Still, this is what I found:

 

Before:

tomfox_0-1664126075107.png

 

 

After:

tomfox_1-1664126091976.png

 

There is no "find text and substitute/remove" function for titles in Power Query. But you could go into the M code of the advanced editor and do the copy and pasting there. I imagine that could save you a bit of time:

tomfox_2-1664126227133.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWJVjICsszALGMgyxzMMgGyLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Deaths - Alzheimer's disease and other dementias Sex: Both - Age: All Ages (Number)" = _t, #"Deaths - Mengitis disease and other dementias Sex: Both - Age: All Ages (Number)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Deaths - Alzheimer's disease and other dementias Sex: Both - Age: All Ages (Number)", Int64.Type}, {"Deaths - Mengitis disease and other dementias Sex: Both - Age: All Ages (Number)", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{
        {"Deaths - Alzheimer's disease and other dementias Sex: Both - Age: All Ages (Number)", "Alzheimer's disease and other dementias"},
        {"Deaths - Mengitis disease and other dementias Sex: Both - Age: All Ages (Number)", "Mengitis disease and other dementias"}
        })
in
    #"Renamed Columns"

 

As you can see, you can specify the renaming of many columns in just one steep. Another option could also be to change the name of the columns in your source system, i.e. by aliasing them accordingly in a view. 

 

Hope this helps! 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




tackytechtom
Super User
Super User

Hi @sheryl_1798_bb ,

 

Please, could you share a screenshot of the data and what you exactly want to remove? There are different ways of doing this and I'd like to see what kind of cases appear in your data so you can solve this in a scalable manner 🙂

 

Thanks!

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors