The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello,
I want to clean a DB in query i'm trying to remove text in a specifit cells,
like this:
DATE | COUNTRY | Code |
12/07/2021 | MEXICO | 001- Chocolate |
12/07/2021 | USA | 02- Sugar |
12/07/2021 | CANADA | 03A01- Candy |
12/07/2021 | Mexico | Water |
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:
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.
Solved! Go to Solution.
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.
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"
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! |
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
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.