Forum Discussion

Ennygreat's avatar
Ennygreat
New Member
2 years ago
Solved

Please I need help

 

 

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. 

Need Help
Tips and Tricks
  • GauravAher's avatar
    GauravAher
    2 years ago

    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

     

     

     

  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    You can add a column using each Text.Trim([Region]), or just select the Region column and use the Trim GUI command.

     

    --Nate

  • GauravAher's avatar
    GauravAher
    New Member
    2 years ago

    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

     

     

     

  • Ennygreat's avatar
    Ennygreat
    New Member
    2 years ago

    Someone should please attend to this if you know how to solve it. Thank you 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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