Forum Discussion
Find and extract a value
Hi,
I wonder if you could help me with the following challenge:
I have 30 columns, called Bag Collection materials and quatities 1, 2, 3.....30. In each column I need to find and extract a value(s) that is higher than 0. The columns look like this:
The end result should look like this
Bag Collection materials and quatities 1
1 Plaster
1 Plaster, 2 hebel
And similar for each column
The only was I know how to do it is using replace value. Which is using replace "Ox timber" with "" and this for each material for each column.
Can somebody let me know the best way to do this?
Thank you
Mirna
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
6 Replies
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,
SpyrosSorry for the delay response, thanks so much for your help,it worked!
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,
Hi Mirna
1. Can you share the original file? It would be much quicker if we have it. You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
2. The number you are looking for is always before the "x"?
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
