cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI_Analyticz
Helper V
Helper V

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 
2 ACCEPTED SOLUTIONS

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"

 

Capture5.PNG

 

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

View solution in original post

@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

View solution in original post

11 REPLIES 11
BI_Analyticz
Helper V
Helper V

Friends any ideas please

@BI_Analyticz 

here is a workaround for you.

1. select the first column and unpivot other columns

1.PNG

2. filter out blank values

2.PNG

3. group by

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

3.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 

@BI_Analyticz 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

 

 

@BI_Analyticz 

does this your rawdata look like?

1.PNG

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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%

@BI_Analyticz 

Rawdata:

1.PNG

output:

2.PNG

is this what you want?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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"

 

Capture5.PNG

 

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

View solution in original post

@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

View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors