Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
KasperS
Regular Visitor

How to get specific values from records within a list

Hi

 

I have a dataset containing information on a lot of systems we use in my organization. Each row equals to one system with the columns being information about a given system. I have a column called "Kontakter" (Contacts) which contains lists with n number of records in it, containing contact information about employees working on that system such as: Ansvarsområde (area of responsibility), navn (name), and mail. I want to extract the names of every employee that in the "area of responsibility" field has the value "Systemejerkontorchef" to a new column. How can that be done? 

KasperS_0-1647420656195.pngKasperS_1-1647420680507.png

KasperS_2-1647420830338.png

 

1 ACCEPTED SOLUTION

Hi Kasper 

 

You can create a function to help you do this

 

(listToSearch as any, searchFor as text) =>
let
    Source = listToSearch,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Ansvarsområde", "Name"}, {"Ansvarsområde", "Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([Ansvarsområde] = searchFor)),
    Custom1 = Table.ToRecords( #"Filtered Rows")
in
    Custom1

 

and then invoke that  as a custom function to your table

donsvensen_1-1647426885344.png

 

the function returns all the contacts with the title so by using {0} I only return the first in this case.

 

/Erik

 

 

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I tried to reconstruct your situation (if you want a precise answer this is a job you should do) and starting from this I tried to simulate what you ask.
Try using the yourtab query by replacing "Origin" with your real table.

Hi Kasper 

 

You can create a function to help you do this

 

(listToSearch as any, searchFor as text) =>
let
    Source = listToSearch,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Ansvarsområde", "Name"}, {"Ansvarsområde", "Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([Ansvarsområde] = searchFor)),
    Custom1 = Table.ToRecords( #"Filtered Rows")
in
    Custom1

 

and then invoke that  as a custom function to your table

donsvensen_1-1647426885344.png

 

the function returns all the contacts with the title so by using {0} I only return the first in this case.

 

/Erik

 

 

 

You should be able to pull specific parts from the record quite easily.


Create a new custom column.

Gospoole_0-1695304568813.png

 

Name it something appropriate

In the syntax box you should put something like this if for example you are pulling the Name of someone:

= [Column Name][Title]

Gospoole_1-1695304621079.png

or if its the email you want

=[Column Name][Email]

 

To check what the field in the record is called click the record in the power query and then simply delete that step to return to the query and start your column creation.

Gospoole_2-1695304647939.png

 

Nice and simple!

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors