Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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
the function returns all the contacts with the title so by using {0} I only return the first in this case.
/Erik
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
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!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
63 | |
40 | |
28 | |
17 |