Getting a specific row value from a list

[garyhalb]

Hi,

I've managed to get my data into the following table format:

I'm trying to add two new columns to this dataset based on row values that come from the highlighted column above. You'll notice it's a list of name value pairs per row. How do I go about extracting a specific value from the list for each row, and make this a new column in the main dataset?

Thanks.

[Anonymous]

Maybe you can try to extend list to rows first and then extract a specific value from it?

[garyhalb]

Yes, that's what I thought I'd try and do, but then how do I get that value as a column in the original data?

[Anonymous · Accepted solution]

After expending the column, you can use Merge Queries feature to add the column into original table. See if it helps.

You can refer to MS doc for the details.

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

[garyhalb]

Thanks Jessica. This worked. I had to expand the column, then filter to a specific item in the list and then merge. But this looks like it's working.

[v-yuezhe-msft]

@garyhalb,

Which sample data do you store in the lists and which values would like to show in new column? If you want to create new column on the basis that check if a value is contained in the list, you can use List.Contains() function.

However, if you want to extract specific value from the list, you can firstly expand all values from the list to new rows, create a new column using Text.Contains() function to check if specific value exists in each row, then filter all the required rows.


Regards,
Lydia