+ Reply to Thread
Results 1 to 12 of 12

Replace spaces with an asterisk in Power Query

  1. 05-30-2025, 12:02 PM #1
    Bleucherie
    Bleucherie is offline
    Registered User
    Join Date
    02-20-2015
    Location
    NY
    MS-Off Ver
    Microsoft 365 Version 2505 Build 16.0.18827.20102 64-bit
    Posts
    39

    Replace spaces with an asterisk in Power Query

    Hello,

    I am looking to find a way to find and replace spaces in a cell with an asterisk so that I can run the rest of the Power Query steps of my template. From the attached file I am looking to add a step where if there is a "Remark=" text in the Remarks 10 column it would replace the spaces in between the text/numbers to asterisks as noted in the Expected Result column. I was looking at the Find and Replace function but since the Remarks 10 column can include other descriptions it is not going to work well. If it is easier as an Excel formula I can try that too but would prefer to keep everything in Power Query. Thanks!

    Replace Spaces File.xlsx
    Reply With QuoteRegister To Reply
  2. 05-30-2025, 12:24 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,011

    Re: Replace spaces with an asterisk in Power Query

    Basic formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Use BYROW to propagate it.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 05-30-2025, 12:27 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,011

    Re: Replace spaces with an asterisk in Power Query

    Clear expected results and use:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 05-30-2025, 12:47 PM #4
    Bleucherie
    Bleucherie is offline
    Registered User
    Join Date
    02-20-2015
    Location
    NY
    MS-Off Ver
    Microsoft 365 Version 2505 Build 16.0.18827.20102 64-bit
    Posts
    39

    Re: Replace spaces with an asterisk in Power Query

    Quote Originally Posted by TMS View Post
    Clear expected results and use:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Thank you this worked! Is there a way to incorporate it into Power Query?
    Reply With QuoteRegister To Reply
  5. 05-30-2025, 01:32 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,011

    Re: Replace spaces with an asterisk in Power Query

    I have no idea. I know absolutely nothing about Power Query.

    I'm sure it's doable, just not by me.
    Reply With QuoteRegister To Reply
  6. 05-30-2025, 03:01 PM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,228

    Re: Replace spaces with an asterisk in Power Query

    In PQ

    Select "REMArks 10" column>>Transform>>Replace Values>> Value to find .. Blank, Replace with ..*

    From a PQ novice!
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  7. 05-30-2025, 03:07 PM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,011

    Re: Replace spaces with an asterisk in Power Query

    @JT: from someone who knows nothing about PQ, how does that cater for the rows that don't start with REMARK?
    Reply With QuoteRegister To Reply
  8. 05-30-2025, 03:27 PM #8
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,228

    Re: Replace spaces with an asterisk in Power Query

    Doh ... missed the REMARKS constraint!
    Reply With QuoteRegister To Reply
  9. 05-30-2025, 03:36 PM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,011

    Re: Replace spaces with an asterisk in Power Query

    Sorry 😔. Maybe your chance to enhance your PQ skills.
    Reply With QuoteRegister To Reply
  10. 05-30-2025, 03:36 PM #10
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,228

    Re: Replace spaces with an asterisk in Power Query

    Actual quite easy

    ADD COLUMN>>CONDITIONAL COLUMN>> Enter parameters >>>with rows starting with REMARK: Select REMARK rows :Remove unwanted columns and then REPLACE on new column

    If I can do it, then any anyone with basic PQ knowledge should be able to !
    Attached Images Attached Images
    Last edited by JohnTopley; 05-30-2025 at 03:47 PM.
    Reply With QuoteRegister To Reply
  11. 05-30-2025, 03:50 PM #11
    Bleucherie
    Bleucherie is offline
    Registered User
    Join Date
    02-20-2015
    Location
    NY
    MS-Off Ver
    Microsoft 365 Version 2505 Build 16.0.18827.20102 64-bit
    Posts
    39

    Re: Replace spaces with an asterisk in Power Query

    I was able to get the PQ solution for this, I would use the following:

    = Table.ReplaceValue(#"Changed Type",each [Remarks 10],null,(x,y,z) as text=> if Text.StartsWith(y,"REMARK=TRN") then Text.Replace(x," ","*") else x,{"Remarks 10"})

    I adjusted it to look for those with REMARK=TRN as there were other lines with the same start text but didn't have TRN in it.

    Thank you all for your help!
    Reply With QuoteRegister To Reply
  12. 05-30-2025, 04:02 PM #12
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,011

    Re: Replace spaces with an asterisk in Power Query

    You're welcome.



    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Power Query try otherwise with text replace
    By Dicken in forum Office 365
    Replies: 3
    Last Post: 03-13-2024, 10:00 AM
  2. [SOLVED] Power Query excel - vlookup and replace
    By afgi in forum Excel General
    Replies: 3
    Last Post: 01-05-2024, 05:41 AM
  3. replace Power Bi, using Power automate, Excel, Power Query and onedrive/sharepoint
    By gustavo_adolfo in forum Office 365
    Replies: 5
    Last Post: 10-31-2023, 11:12 AM
  4. [SOLVED] Replace multiple values in power query
    By Mayasak in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-22-2023, 01:35 PM
  5. Replace formula using power query
    By Jclarka in forum Excel General
    Replies: 1
    Last Post: 09-12-2022, 06:42 PM
  6. [SOLVED] Append and replace in Power Query
    By punter in forum Excel General
    Replies: 3
    Last Post: 04-08-2020, 01:25 AM
  7. Power Query to replace macros
    By lynnsong986 in forum Excel General
    Replies: 9
    Last Post: 11-12-2019, 10:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1