Removal of repetitive words in the title

[sheryl_1798_bb]

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'?

[sheryl_1798_bb]

Hi @tackytechtom! 

Thanks for reaching out! 

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.

[tackytechtom · 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:

After:

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:

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/

[tackytechtom]

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/