Unable to transform this simple data into the below Layout.

HI iamprajot ,

You can take a look at following pbix file to get transformed table formula.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xiaoxish\Downloads\Data.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Removed Columns"=Table.RemoveColumns(Table.FillDown(Table.Transpose(Table.SelectColumns(Data_Sheet,{"Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18"})),{"Column1"}),{"Column2"}),
    Custom1 = Table.AddColumn(Table.PromoteHeaders(Table.RemoveFirstN(Table.SelectColumns(Data_Sheet,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),2)),"Custom",each Table.ExpandTableColumn(Table.Group(#"Removed Columns", {"Column1"}, {{"Count", each Table.Distinct(Table.PromoteHeaders(Table.Transpose(Table.RemoveColumns(_,"Column1")))), type table }}), "Count", {"Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"}, {"Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"})),
    #"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"Column1", "Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"}, {"Column1", "Dimension", "Qty", "Area", "Coverage Calculation", "Material", "Subtotal"})
in
    #"Expanded Custom"

Notice: it is hard complex to use power query functions to format pivot table, if you can please do these on excel worksheet side. (It should more simple to do)

Regards,

Xiaoxin Sheng