Forum Discussion
Partial match from 3 column with text
Please remember to @ when replying. Otherwise we do not get notified of your response.
If this is coming from a SQL source, I think that would be where you would want to perform something with the complexity of 200 individual texts and 14 columns.
Other option is to split your Delimiter into rows rather than columns.
On the Delimiter, selecting "advance options" gives you the option of rows vs columns.
For example, Record X 11 would have 200 corresponding rows.
You can Concatenate each of your 14 Columns together into fewer columns - whatever character limitations you might bump up against.
Then create a Match column that looks to find each row of text in these new Concatenated columns.
Still a little cumbersome, but better deal with fewer columns and many rows than the other way around.
Regards,
Community ChampionYou can accomplish this using Power Query.
1) Split column 1 using the comma as a Delimiter
2) Then add a Custom Column which searches in Column 2 for any of the values in the columns created by the Delimiter
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTI0BBIgRkHi0V0pVZmpRXmZ2UCugaGeoYGekYGREQpHwcjAytTcysBAB03MAouYJVCMCN2GWHQbQnXH6kCcCXYGFmcamSE5E8FRMDK2MjQGGYomZgJxErKoER6VIOtxWGyJbDGco2BgYWVoADYOVcwQi5gR1DGoosbE6Lakiu7YWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Place = _t, #"Worker code" = _t, Zespół = _t, Month = _t, Date = _t, #"1" = _t, #"2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Place", type text}, {"Worker code", type text}, {"Zespół", type text}, {"Month", type text}, {"Date", type text}, {"1", type text}, {"2", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"1.1", "1.2", "1.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"1.1", type text}, {"1.2", type text}, {"1.3", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Match", each if Text.Contains( [2], [1.1] )
or Text.Contains( [2], [1.2] )
or Text.Contains( [2], [1.3] )
then 1
else 0)
in
#"Added Custom"
I have assumed Column 1 always has 3 values. If not you will need to modify the OR conditions in the Custom Column called Match
Hope this provides you the guidance you were looking for.
Regards,
Thank you!
Any idea how do this with first column with 200 separate text and 14 columns to compare?
- rsbin
Please remember to @ when replying. Otherwise we do not get notified of your response.
If this is coming from a SQL source, I think that would be where you would want to perform something with the complexity of 200 individual texts and 14 columns.
Other option is to split your Delimiter into rows rather than columns.
On the Delimiter, selecting "advance options" gives you the option of rows vs columns.
For example, Record X 11 would have 200 corresponding rows.
You can Concatenate each of your 14 Columns together into fewer columns - whatever character limitations you might bump up against.
Then create a Match column that looks to find each row of text in these new Concatenated columns.
Still a little cumbersome, but better deal with fewer columns and many rows than the other way around.
Regards,
rsbin thank you, it works great with rows 🙂