Forum Discussion
Find and extract a value
Hi Mirna,
If your data comes always in the form of "0x tile, 0x timber, 0x brick, 0x plaster, 0x hebel, 0x mixed", then this should work as intented for any number of columns named [Bag Collection Materials And Quantities x].
Columns = List.FindText(Table.ColumnNames(PreviousStep), "Bag Collection Materials And Quantities "), #"Replace timber" = Table.ReplaceValue(PreviousStep, " 0x timber,", "", Replacer.ReplaceText, Columns), #"Replace brick" = Table.ReplaceValue(#"Replace timber", " 0x brick,", "", Replacer.ReplaceText, Columns), #"Replace plaster" = Table.ReplaceValue(#"Replace brick", " 0x plaster,", "", Replacer.ReplaceText, Columns), #"Replace hebel" = Table.ReplaceValue(#"Replace plaster", " 0x hebel,", "", Replacer.ReplaceText, Columns), #"Replace mixed" = Table.ReplaceValue(#"Replace hebel", ", 0x mixed", "", Replacer.ReplaceText, Columns), #"Replace x" = Table.ReplaceValue(#"Replace mixed", "x ", " ", Replacer.ReplaceText, Columns), #"Replace tile" = let Remake = (BeforeTable as table, n as number) => if n > 0 then let Substitute = Table.TransformColumns(BeforeTable, {{"Bag Collection Materials And Quantities " & Number.ToText(n), each if Text.StartsWith(_, "0 tile") then Text.AfterDelimiter(_, "0 tile, ") else _}}), Rest = @Remake(Substitute, n-1) in Rest else BeforeTable in Remake(#"Replace x", List.Count(Columns)) in #"Replace tile"Where PreviousStep is, as it says, your previous step. Tell me if you'd like me to explain something.
Cheers,
Spyros
Hi Mirna
If your columns only have items as the screenshots show, then Smauro's answer may be very useful.
If not, please let us know.
Best Regards
Maggie
Hi Mirna
1. First create a function with this code and name it "processTextFunct"
(inputText_ as text) as text =>
let
l1_ = Text.Split(inputText_, ", "),
l2_ = List.Select(List.Transform(l1_, each Text.Split(_, "x ")),each Number.From(_{0})>0),
res_ = Text.Combine(List.Transform(l2_, (inner)=> inner{0} &" " &inner{1}),", ")
in
res_
2. Then you can use that function within Table.TransformColumns( ) to obtain the result that you need for all columns. Copy the following code in a blank query to see the steps based on a three-column table similar to yours:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqxQKMnMSdVRMAAxcpNSi8DMgpzE4hIQ27hCISM1KTUHLJqbWZGaoqSjZESOJmPSNcXqRCsZwLQZkmAXvTSR5kBDcuyiTFNsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Bag Collection materials and quatities 1" = _t, #"Bag Collection materials and quatities 2" = _t, #"Bag Collection materials and quatities 3" = _t]),
t2_ = Table.TransformColumns(Source, List.Transform(Table.ColumnNames(Source), each { _ , processTextFunct}) )
in
t2_
Note that the code above performs the transformation on all columns of the starting table. If you want to limit it to a subset of the columns you could either filter the result of Table.ColumNames( ) or create a list, manually or programmatically, with the names of the columns you want to process.
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Text.Split is a great idea.
A quicker way, and keeping the Columns changed to the ones like "Bag Collection Materials And Quantities " would then be to add the function on the previous step and just call it on the next step. I've also used List.Accumulate instead of Text.Combine and List.Transform:let PreviousStep = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqxQKMnMSdVRMAAxcpNSi8DMgpzE4hIQ27hCISM1KTUHLJqbWZGaoqSjZESOJmNyNBkqxepEKxnAtBqSoJV+moxIdKQhOfZRqslYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Bag Collection Materials And Quantities 1" = _t, #"Bag Collection Materials And Quantities 2" = _t, #"Bag Collection Materials And Quantities 3" = _t, Column1 = _t]), Columns = List.FindText(Table.ColumnNames(PreviousStep), "Bag Collection Materials And Quantities "), fnFix = let fix = (t as text) => Text.AfterDelimiter(List.Accumulate(List.Select(Text.Split(Text.Replace(t, "x ", " "), ", "), each not Text.StartsWith(_, "0")), "", (state, current) => state & ", " & current), ", ") in fix, FixCols = Table.TransformColumns(PreviousStep, List.Transform(Columns, each { _ , fnFix}) ) in FixColsCheers,