Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Lookup the cells in same row

I have a crazy dataset like below..

 

In the Col 10, I want to lookup the previous cells in the same row like if any of the cell contains OS: then in Col 10 put the value. 

But some rows having 3 values for OS: in different columns for some reasons. In that case simply concatenate using a delimter and put it together in the same cell.

 

Is this possible 

 

Col 1Col 2Col 3Col 4Col 5Col 6Col 7Col 8Col 9Col 10Col 11
JackOS: Windows       Windows 
Rick OS: Windows  OS: Windows 2000  OS: Windows 2000 ServerWindows| Windows 2000|Windows 2000 Server 
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    5 years ago

    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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Friends any ideas please

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      5 years ago

      Anonymous 

      here is a workaround for you.

      1. select the first column and unpivot other columns

      2. filter out blank values

      3. group by

      = Table.Group(#"Filtered Rows", {"Column1"}, {{"Count", each Text.Combine([Value],"|"), type text}})

      if you dont want to see OS, you can remove all OS, I didnt write OS in the sample data

      please see the attachment below

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Apologies Ryan. I should have mentioned earlier. My data was originally started from Step 3. I mean it was like in step 3 then splitted using delimenter. But the issue is the data was there like below.

         

        If you can look at the 2nd row in between cells are having different attributes so I was not able to simply merge easily and this keeps varying. For some rows the OS values are in Col 2 and for some in Col3 and for some in Col 5 and Col 6 with some othr attribute in Col 4 . Hence I thought if we can do a text search or look up or any other possiblty to capture the OS value.. Is it possible..

         

        Col 1Col 2Col 3Col 4Col 5Col 6Col 7Col 8Col 9Col 10Col 11
        JackOS: Windows       Windows 
        Rick OS: WindowsType: DesktopLoc: DenverOS: Windows 2000  OS: Windows 2000 ServerWindows| Windows 2000|Windows 2000 Server