Forum Discussion

KasperS's avatar
KasperS
Regular Visitor
4 years ago
Solved

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 ca...
Need Help
  • donsvensen's avatar
    donsvensen
    4 years ago

    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

     

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

     

    /Erik

     

     

     

donsvensen's avatar
donsvensen
Skilled Sharer
4 years ago

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

 

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

 

/Erik

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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


    Create a new custom column.

     

    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]

    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.

     

    Nice and simple!