extract particular value from list record column in power query

[vijaykaali811]

i have column additionaldata as list type , that  list contains key value records like "OS" :rhel , "version","rhel8 like  that .

without expanding all list and record as it is creating duplicate record . i just want to loop through list and  find record  with  OS as ket and get the value . is it possible 

[v-hashadapu]

Hi @vijaykaali811 , Thank you for reaching out to the Microsoft Community Forum.

I recommend using a custom column in Power Query with a let...in function to safely extract the "OS" value. This method uses List.Select to filter records with the "OS" key and List.IsEmpty to handle cases where the key is missing, returning null instead of errors. Example:
let

    FindOS = (list) =>

        let

            Filtered = List.Select(list, each Record.HasFields(_, {"OS"})),

            Result = if List.IsEmpty(Filtered) then null else Filtered{0}[OS]

        in

            Result

in

    FindOS([additionaldata])

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[rohit1991]

Hi @vijaykaali811 ,
Yes, it's absolutely possible to extract a specific value—like the one associated with the key "OS"—from a list of records in a Power Query column without expanding the entire list, which can otherwise lead to unwanted duplication of rows. Instead of expanding the full list, you can create a custom column using M code that filters the list for records containing the key "OS", then pulls out the corresponding value. This approach is much cleaner and keeps each row intact while still allowing you to access the information you need from within the list structure.

List.First(
    List.Transform(
        List.Select([additionaldata], each Record.HasFields(_, "OS")),
        each _[OS]
    )
)

[Ritaf1983]

Hi @vijaykaali811 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[danextian · Accepted solution]

Hi @vijaykaali811 

To get a specific item from a list without expanding it, you can use access the position of an item - which always begins at zero. Assuming List of Records is the name of the list column, you can create a custom column with this formula

[List of Records]{0} // This retrieves the first record from the "List of Records" column

[List of Records]{0}[OS] // This accesses the value associated with the "OS" key in the first record of the "List of Records" column

Sample query:

let
    Source =
        Table.FromRecords({
            [ID = 1, List of Records = { [OS = "rhel"], [version = "rhel8"], [Arch = "x86_64"] }],
            [ID = 2,  List of Records = { [OS = "ubuntu"], [version = "20.04"], [Arch = "arm64"] }],
            [ID = 3,  List of Records = { [OS = "centos"], [version = "7"], [Arch = "x86_64"] }]
        }),
    #"Added Custom" = Table.AddColumn(Source, "OS", each [List of Records]{0}[OS]),
    OS = #"Added Custom"{1}[OS]
in
    OS

[rajendraongole1]

Hi @vijaykaali811 -could you send over a small sample of your data? Even 3–5 example rows with the list structure would be great.

Thank you