Lists in Power Query

A list in Power Query has only a single column of data, whereas a table can contain more than one column also. The list can be created manually using the curly brackets {}. The values in the list are separated by commas.

Syntax

Example: Following is the list of country values, which contains 6 values.

Power Query M

= {"India", "America", "Canada", "Japan", "Australia", "England"}

The output of the above code is shown below:

Extract column from a table as List

If we want to extract a column from a table as a list, we can do it by the help of the following syntax:

Syntax TableName[Column_Name] as a list

Note: We can also extract a column from a table as a list using the Table.Column function.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = "Katrina", Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 367], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = "Mohini", Marks = 278], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = 875], 
      [CustomerID = 8, Name = "Anjali", Marks = 380]
    }
  ), 
  Return = (MyTable)[Name]
in
  Return 

The output of the above code is shown below:

Access Values from Lists

We can access the list elements by using the index position of the item in the list. The first item in the list has index 0.

To access the element, use the following syntax: InputList{index_position}

It will return the item at the specified index position from the Input list.

Example: We have a list with three values.

Power Query M

let
  Source = ({{"Ashish", "Alia"}, "Kiara", {"Haryana", "Mumbai"}})
in
  Source  

The output of the above code is shown below:

To retrieve the first item from the list, we can use the following formula:

Power Query M

let
  Source = ({{"Ashish", "Alia"}, "Kiara", {"Haryana", "Mumbai"}}),
  return = Source{0}
in
  return   

The output of the above code is shown below:

Suppose in our example, if I want to extract “Alia”. Then it means it is second item of the first item of the input list. To do this, just specify the cascaded index position within the curly braces {}.

Power Query M

let
  Source = ({{"Ashish", "Alia"}, "Kiara", {"Haryana", "Mumbai"}}),
  return = Source{0}{1}
in
  return   

The output of the above code is “Alia”.