Set a Single Value as the Row Header for every row - Power Query

The source data has the Organization Name in the first row, followed by Product and Version details in the subsequent rows.

The goal is to convert this into a three-column table, with each column representing Organization, Product, and Version information.

Row Header.xlsx

Download XLSX • 16KB

In this post, I will showcase two different methods to achieve this.

Method-1:

let

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

 Result = Table.FromRows(List.Transform(List.Split(List.Skip(Source[Data]),2), (f) => {Table.FirstValue(Source)} & f),{"Org","Product","Version"} )

in

 Result

Method-2:

let

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

 Result = Table.FillDown(Table.FromColumns({{Table.FirstValue(Source)}} & List.Zip(List.Split(List.Skip(Source[Data]),2)),{"Org","Product","Version"}),{"Org"})

in

 Result