cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtp64
New Member

Expand List and match field to show data

I have a table expanded that has an embedded list.  The list contains records. Unfortunately, because the lists are applied to each row, it causes the row to be duplicated. So I need to be able to extract the list into columns and somehow get rid of the duplicate rows.  There are now unique keys for each row.

 

There are three records, key, namespace and value.  I want to be able to expand the list, but create new columns based on each "key", then populate that column with the data in the corresponding value field.  For example, row 1 has list with records of key=account_string, namespace=null (I don't need namespace) and value=45. row 2 may have key=company, value=ab.  I would end up with

account_string      company

     45                       ab

jtp64_0-1635253589104.png

Hope that make sense.  Thanks in advance.

2 REPLIES 2
YukiK
Responsive Resident
Responsive Resident

You can apply "Remove duplicate" on multiple columns (Ctrl + click to choose multiple columns). And then I'd just add an index column or custom column like you mentioned where the column will be mix of your key columns.

 

Hope that makes sense!

Thanks.  My bigger issue is getting the "list" expanded and converted into columns. That is where I am struggling.  I have looked at list.transform, etc but have not had good luck yet.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors