Lookup the cells in same row

[BI_Analyticz]

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 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9	Col 10	Col 11
Jack	OS: Windows								Windows
Rick		OS: Windows			OS: Windows 2000			OS: Windows 2000 Server	Windows| Windows 2000|Windows 2000 Server

[v-deddai1-msft · Accepted solution]

Hi @BI_Analyticz ,

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

[BI_Analyticz]

Friends any ideas please

[ryan_mayu]

@BI_Analyticz 

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

[BI_Analyticz]

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 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9	Col 10	Col 11
Jack	OS: Windows								Windows
Rick		OS: Windows	Type: Desktop	Loc: Denver	OS: Windows 2000			OS: Windows 2000 Server	Windows| Windows 2000|Windows 2000 Server

[ryan_mayu]

@BI_Analyticz 

what about in step 2, filter only windows related results?

[BI_Analyticz]

No in Step 2 nothing. Actually there are different TAGS like OS: , LOC:, TYPE: in the row for each asset. But this order is changing in every row. If the order is not changing then we can merge or group it. 

If the text search or lookup in row is working i have to do the same for all different tags.

[ryan_mayu]

@BI_Analyticz 

does this your rawdata look like?

Do you want to get the result in red? please see if the attachment below helps

[BI_Analyticz]

@ryan_mayu coloring is not needed. The above picture looks exactly like mine but in the second row please change Col 5 from Loc: Denver to OS: Windows Server 2016. Then data matches 100%

[ryan_mayu]

@BI_Analyticz 

Rawdata:

output:

is this what you want?

[v-deddai1-msft · Accepted solution]

Hi @BI_Analyticz ,

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

[BI_Analyticz · Accepted solution]

@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

[BI_Analyticz]

I changed that column to Text and it is working fine.