Find and extract a value

[Mirna]

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

[v-juanli-msft]

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

[AlB]

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 

[Smauro]

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
    FixCols

Cheers,

[Smauro · Accepted solution]

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

[Mirna]

Sorry for the delay response, thanks so much for your help,it worked!

[AlB]

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