Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm trying to work on a column using power query and I encountered an inconsistent data entry. The region doesn't align and was repeated with different space method. Kindly see attached screenshot document the region segment to understand better and if you can profer solution for me. Thank you.
Solved! Go to Solution.
Hi,
To remove leading and trailing spaces from your data, use the Trim function. First, go to the Transform Data page, select the Region column, right-click, choose Transform, and select Trim.
I made a YouTube video on this issue. If my solution helps you, please subscribe and like the video at
https://youtu.be/2H-DASuXF6A
Thank You
Hi @Ennygreat, if you also have spaces between words - you can do this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlBwLC0uKUrMyUxUitWJVgrNyyxJTVHwzsxLT8nPBQspKPilliuAQFRqYk5iXgpUVCE4tSgJpC0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_RemovedSpaces = Table.AddColumn(Source, "Removed Spaces", each Text.Combine(List.RemoveMatchingItems(Text.Split([Column1], " "), {""}), " "), type text)
in
Ad_RemovedSpaces
Hi @Ennygreat, if you also have spaces between words - you can do this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlBwLC0uKUrMyUxUitWJVgrNyyxJTVHwzsxLT8nPBQspKPilliuAQFRqYk5iXgpUVCE4tSgJpC0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_RemovedSpaces = Table.AddColumn(Source, "Removed Spaces", each Text.Combine(List.RemoveMatchingItems(Text.Split([Column1], " "), {""}), " "), type text)
in
Ad_RemovedSpaces
Hi,
To remove leading and trailing spaces from your data, use the Trim function. First, go to the Transform Data page, select the Region column, right-click, choose Transform, and select Trim.
I made a YouTube video on this issue. If my solution helps you, please subscribe and like the video at
https://youtu.be/2H-DASuXF6A
Thank You
Thank you
You can add a column using each Text.Trim([Region]), or just select the Region column and use the Trim GUI command.
--Nate
Someone should please attend to this if you know how to solve it. Thank you