Lookup the cells in same row

Hi Anonymous ,

Are you trying to create a new column to combine all other columns? You don't need to unpivot the columns , just use the following m query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kpMzlbSUfIPtlIIz8xLyS8vBvJQUKxOtFJQJlgVhsKQyoJUH5fU4uyS/AKIJFROITi1qCy1SMHIwNAMVcLIwMAAm1Kl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    result = Table.AddColumn(#"Changed Type","column10",each Text.Combine(List.RemoveNulls(List.Select(Record.ToList(_),(x)=>Text.Contains(x,"OS"))),"|")),
    #"Replaced Value" = Table.ReplaceValue(result,"OS:","",Replacer.ReplaceText,{"column10"})
in
    #"Replaced Value"

Please refer to the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

v-deddai1-msft  Thank You and I tried the code. It is giving me error as shown below. I cannot figure out to move further.

Expression.Error: We cannot convert the value 103068199 to type Text.
Details:
Value=103068199
Type=[Type]

I have a column ID and one of the cell has 103068199 as the value. Can you please help