Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors