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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Replacing column name text in each column

Hi,

I would like to remove the column name text if it is found in each column as follows.

Replace Text.png

Any idea how this can be achieved in Power Query?

Many thanks.

1 ACCEPTED SOLUTION
Jakinta
Solution Sage
Solution Sage

You can try this in new blank query.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQquLDaMj0wthjCNEExjMDNWJ1rJiDhlxkABiDCCBImbQNjxICMQTCME0xiszJQIW2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Sys1 = _t, Sys2 = _t, Sys3 = _t]),
    Custom1 = let l=Table.ToColumns(Source), cols=Table.ColumnNames(Source), i= List.Positions(cols), new= List.Transform (i, each List.ReplaceValue( l{_}, cols{_}&"_","", Replacer.ReplaceText) ) in Table.FromColumns(new, cols)

in
    Custom1

 

Start:

Jakinta_0-1633254972772.png

End:

Jakinta_1-1633254990912.png

 

 

 

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

@jshwong 

You can either right-click on the column, select Replace Value and Enter "Sys1_" with blank

Fowmy_0-1633254549844.png


Or, Under Transform Tab, Click Extract and select Text After Delimiter 

Fowmy_1-1633254631042.png

 

 

 

Fowmy
Super User
Super User

@Anonymous 

You can either right-click on the column, select Replace Value and Enter "Sys1_" with blank

Fowmy_0-1633254549844.png


Or, Under Transform Tab, Click Extract and select Text After Delimiter 

Fowmy_1-1633254631042.png

 

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Jakinta
Solution Sage
Solution Sage

You can try this in new blank query.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQquLDaMj0wthjCNEExjMDNWJ1rJiDhlxkABiDCCBImbQNjxICMQTCME0xiszJQIW2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Sys1 = _t, Sys2 = _t, Sys3 = _t]),
    Custom1 = let l=Table.ToColumns(Source), cols=Table.ColumnNames(Source), i= List.Positions(cols), new= List.Transform (i, each List.ReplaceValue( l{_}, cols{_}&"_","", Replacer.ReplaceText) ) in Table.FromColumns(new, cols)

in
    Custom1

 

Start:

Jakinta_0-1633254972772.png

End:

Jakinta_1-1633254990912.png

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors