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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
wi11iamr
Advocate II
Advocate II

Replace carriage return in dataset

Hi there,

 

I have some columns in my dataset that has a large amount of text that includes a carriage return for some records.

 

Does anyone know of some interesting techniques I could try to replace the carriage return with a comma?

 

Thanks

W

1 ACCEPTED SOLUTION

@wi11iamr It would appear you can do this in the Desktop.

In the Data section, click "Edit Queries" in the ribbon.

Select the table you want, and right click the column header

Select "Replace Values", click in the "Value To Find"

Select "Advanced Options" check "Replace using special characters"

Select "Insert special character"

Click "Carriage Return" and a carriage return should show in your values section

put comma in "Replace With"

OK

Close and Apply

 

This is "in theory" as I didn't directly test it, but the sequence appears to support the action.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

5 REPLIES 5
aggregator3000
Advocate II
Advocate II

Bumping this old one for anyone wanting to do this in DAX.
Note, Line feed and Carriage Return are different to eachother.
I only know this after dealing with a string that had both... yep


You can use either of following, or combine them

 

Substitute out the carriage = 
    SUBSTITUTE( [TestCarriage],
                unichar(13),
                "\") --Replacement here

Substitute out the Line Feed = 
    SUBSTITUTE( [TestLineFeed],
                unichar(10),
                "\") --Replacement here

 

@wi11iamr It would appear you can do this in the Desktop.

In the Data section, click "Edit Queries" in the ribbon.

Select the table you want, and right click the column header

Select "Replace Values", click in the "Value To Find"

Select "Advanced Options" check "Replace using special characters"

Select "Insert special character"

Click "Carriage Return" and a carriage return should show in your values section

put comma in "Replace With"

OK

Close and Apply

 

This is "in theory" as I didn't directly test it, but the sequence appears to support the action.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thanks Eno, the solution you propose is however unfortunately to replace characters with a carriage return. My dilemma is to replace a carriage return with a character.

Anonymous
Not applicable

@wi11iamr

 

i think @Seth_C_Bauer solution should work for replacing with a character 

below image did that when i have it for line feed and replaced it with '-'

 

Replace.PNG

After

 

Replace After.PNG

Thank you, it appears I was too hasty (and naieve) in my initial response.

 

@Seth_C_Bauer - your clarification was indeed correct, thank you.

@Anonymous - thank you for taking the time to point this out to me

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.