Power Query - Extract values from lists and records in adjacent column

Power Query
2

Hi @Mo.jo.jo,

Use Record.Field to return a value from a specific field in a record.

Copy this into a new blank query.

let
    Source = #table( type table[Company=record, Created By=list], 
        {
            { Record.FromList({1, "Bob", "123-4567"}, {"__type", "Label", "TermID"}), 
              Table.ToRecords( Table.FromRows( {{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}, {3, "Paul", "543-7890"}}, {"CustomerID", "Title", "Phone"}))
            },
            { Record.FromList({2, "Jim", "987-6543"}, {"__type", "Label", "TermID"}), 
              Table.ToRecords( Table.FromRows( {{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}, {3, "Paul", "543-7890"}}, {"CustomerID", "Title", "Phone"}))
            },
            { Record.FromList({3, "Paul", "543-7890"}, {"__type", "Label", "TermID"}), 
              Table.ToRecords( Table.FromRows( {{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}, {3, "Paul", "543-7890"}}, {"CustomerID", "Title", "Phone"}))
            }
        }),
    AddLabel = Table.AddColumn(Source, "GetLabel", each Record.Field([Company], "Label")),
    AddIndex = Table.AddIndexColumn(AddLabel, "Index", 0, 1, Int64.Type),
    AddTitle = Table.AddColumn(AddIndex, "GetTitle", each Record.Field([Created By]{[Index]}, "Title"))
in
    AddTitle

.
I hope this is helpful

4 Likes