Generate Multi-Column Table from List in One Step - Power Query

Today, we will discuss about Table.FromList function in power query and its capability to create multiple columns from a list.

Method-1:

We have dates as our input data, and we plan to extract attributes such as Month Name, Quarter, and Week Number from these dates across multiple columns.

While this can be achieved through the user interface, adding n columns would require n steps. However, we can accomplish this in a single step.

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Result = Table.FromList(Source[Date], each {Date.From(_),

Text.Start(Date.MonthName(_),3),"Q" & Text.From(Date.QuarterOfYear(_)),

Date.WeekOfYear(_)},{"Date","Month","Qtr","Week"})

in

  Result

Method-2:

The input data we have includes date, product, region, and sales information in a single column.

We need to create a table with Date, Product, and Sales, which can be done in one step using the Table.FromList function.

let

Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

Result = Table.FromList(List.Transform(Source[Data],each Text.Split(_,"|")), each

{Date.From(_{0}),_{1}, Number.From(_{3})},

type table [Date = date, Product = text, Sales = number])

in

Result