let Source = (t as table, FirstNColumnsToKeep as number, FirstNRowsToSkip as number)=> let // ------------------------------------------------------------------------------------------------------------------------ // This part creates a fake, one row table with column names like in original table ( first row (the only row) will be promoted later ) Tab = Table.FirstN(t, FirstNRowsToSkip), Custom1 = Table.Transpose(Tab), ChangedType = Table.TransformColumnTypes(Custom1,List.Transform(Table.ColumnNames(Custom1), each {_, type text})), AllColumnNames = Table.ColumnNames(Tab), ConstColumnNames = List.FirstN(AllColumnNames,FirstNColumnsToKeep), ListOfTransposedColumnNames = Table.ColumnNames(ChangedType), // ------------------------------------ // The line below is not necessary if null is real null value and not "null" (like in Table1) #"Replaced Value" = Table.ReplaceValue(ChangedType,"null",null,Replacer.ReplaceValue, ListOfTransposedColumnNames), // ----------------------------------- ReadyToMerge = Table.FillDown(#"Replaced Value", ListOfTransposedColumnNames), #"Merged Columns" = Table.CombineColumns(ReadyToMerge, ListOfTransposedColumnNames, Combiner.CombineTextByDelimiter("$_$", QuoteStyle.None),"MergedColumns"), #"Filtered Rows" = Table.Skip(#"Merged Columns", FirstNColumnsToKeep)[MergedColumns], AllNewNames = Table.FromRows({ConstColumnNames & #"Filtered Rows"}, AllColumnNames), // ------------------------------------------------------------------------------------------------------------------------- // This part combines fake table (AllNewNames) and original table (t) without FirstNRowsToSkip and then it does a few (easy) necessary things Combine = Table.Combine( {AllNewNames, Table.Skip(t, FirstNRowsToSkip)}), #"Promoted Headers" = Table.PromoteHeaders(Combine, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", ConstColumnNames, "Attrib", "Value"), ListOfSplitedColumnNames = List.Transform({1..FirstNRowsToSkip}, each "Attrib." & Text.From(_) ), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attrib",Splitter.SplitTextByDelimiter("$_$", QuoteStyle.Csv),ListOfSplitedColumnNames), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter", List.Transform(ListOfSplitedColumnNames, each {_, type text})) in #"Changed Type" // Documentation ( I've stolen this part from You Imke;-)) , documentation = [ Documentation.Name = " fxUnpivBill ", Documentation.Description = " Unpivots a table according to the number of columns and header rows passed on in the parameters " , Documentation.LongDescription = " Unpivots a table according to the number of columns and header rows passed on in the parameters ", Documentation.Category = " Table functions ", Documentation.Source = " local ", Documentation.Author = " Bill Szysz ", Documentation.Examples = {[Description = " " , Code = " Look at the code in Advanced Editor to read some notes I've stolen this part (Documentation part) from You Imke ;-) ", Result = " "]}] in Value.ReplaceType(Source, Value.ReplaceMetadata(Value.Type(Source), documentation))