Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
The goal is to remove the last comma delimiter of each row. Some rows have one item and is finalized with a comma delimiter. Some rows have multiple items separated by comma delimiters. But all rows have a comma as the final character.
Any advice on how to remove the last comma delimiter of each row is greatly appreciated. Below is sample data for context.
Current Output (notice the last comma of each row)
Test_Data |
Black, |
Black, White, Red, |
Blue, Orange, |
Green, Blue, Black, Red, Orange, |
Expected Output
Test_Data |
Black |
Black, White, Red |
Blue, Orange |
Green, Blue, Black, Red, Orange |
Solved! Go to Solution.
@win_anthony Use the Transform tab. Extract, text before delimiter. Use comma as delimiter and From the end of input and 0.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspJTM7WUYrVgTEVwjMyS1J1FIJSU2DCpUCuf1FiXnoqRMS9KDU1T0cBIgHVBVKOUBQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test_Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test_Data", type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Test_Data", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}})
in
#"Extracted Text Before Delimiter"
@win_anthony Use the Transform tab. Extract, text before delimiter. Use comma as delimiter and From the end of input and 0.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspJTM7WUYrVgTEVwjMyS1J1FIJSU2DCpUCuf1FiXnoqRMS9KDU1T0cBIgHVBVKOUBQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test_Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test_Data", type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Test_Data", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}})
in
#"Extracted Text Before Delimiter"
@Greg_Deckler omg I feel so stupid bc I totally knew that. Regardless, thank you for your advice.
@win_anthony No worries! You shouldn't feel stupid, we all forget stuff we have learned at some point! Happens to me all the time!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |