Merge Cell issue and Unpivot first row only

[Sadath]

Hi
My file format is given below



file loaded in PQ editing is as follows



what will be the M code to unpivot fist row and remove % column
ie. output should be

Parlour Column1 Total Achievd

Name1 Cleanliness 765 530
Name1 Exterior area 778 623....so on..

 

[Comfy]

Here you go:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Parlour", "Column2"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column2]), "Column2", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Name"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"%"})
in
    #"Removed Columns"