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
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
Solved! Go to 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.
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.
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.
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 '-'
After
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
User | Count |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |