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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Remove Some Text for certain Observations

Hello,

I want to clean a DB in query i'm trying to remove text in a specifit cells,

like this: 

DATECOUNTRY Code
12/07/2021

MEXICO

001- Chocolate
12/07/2021USA02- Sugar
12/07/2021CANADA03A01- Candy
12/07/2021MexicoWater

 

I put the simple example beacuse represent the problem, i need to extract Chocolate, Sugar and Candy and Water. Each observation has different long of codes, some has any code.

This was my tries:

  • Divede the colunm by delimiters
  • Transform colum

My idea is the following: Search for text who has a code ( all codes star with 0), then take the text after the delimiter "space", and if it doesn't have a code, just put the same value as in the column

 

I try the next M but i can't be able to make it works:

 

#"New Column" = Table.AddColumn(#"Origen", "New Column", each

                   if Text.StartsWith ([Code],"0" )
                         then Table.TransformColumns(#"Origen", {{"Code", each Text.AfterDelimiter(_, " "), type text}}) 

                   else [Code])

 

It always gives me a column with a table to expand or a function. i need a column,

 

I'm new in M so i can't find the way to resolve that, I apreacciate any idea.

1 ACCEPTED SOLUTION
Migasuke
Memorable Member
Memorable Member

Hi @Anonymous ,

Here is your Power Query step.


#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.StartsWith([Code],"0") then Text.AfterDelimiter([Code]," ")
else [Code])

If it helps, please mark this as a solution.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

let
    LookUp = {"Chocolate", "Sugar", "Candy", "Water", "Egg", "Oil"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzDXNzIwMlTSUfJ1jfB09gcyDAwMdRWcM/KT83MSS1KVYnXQFIYGO4JUGekqBJemJxZhKnB29HN0AasxdgSblZiXUompzDe1IjM5H8goB1oDNCYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, #"COUNTRY " = _t, Code = _t]),
    Split = Table.TransformColumns(Source, {"Code", each List.Select(Text.SplitAny(_, "- "), each List.Contains(LookUp, _, Comparer.OrdinalIgnoreCase))}),
    #"Extracted Values" = Table.TransformColumns(Split, {"Code", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"

Screenshot 2021-07-13 012600.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

I would just append a " " to each value in the column, with the GUI function. Let's say your table or previous step is named LastStep:

 

AddSpace = Table.TransformColumns(LastStep, {{"Code", each Text.Insert(_, 0, " ")}})

 

Then you can use Text.AfterDelimiter to get everything after the first space from the end:

 

Table.TransformColumns (AddSpace, {{"Code", each Text.AfterDelimiter(_, " ", {0,  RelativePosition.FromEnd})}})

 

--Nate

 

 

 

Migasuke
Memorable Member
Memorable Member

Hi @Anonymous ,

Here is your Power Query step.


#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.StartsWith([Code],"0") then Text.AfterDelimiter([Code]," ")
else [Code])

If it helps, please mark this as a solution.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors