Manipulate CSV and create headers

[MikeBlairTP]

My CSV has headers in columns 1 - 9, all the same values.  I have data in columns 10 - 18.  How can I remove the unnecessary headers from the rows and transpose them to be a promoted header with underneath it and not to the right off it

[AlienSx]

@MikeBlairTP Why did you change my code? The very last line (after in) must be 

my_table

[MikeBlairTP]

Perfect @AlienSx a typo on my side, all working now.

[AlienSx]

Hello, @MikeBlairTP try this 

    all_columns = List.Buffer(Table.ToColumns(Source)),
    my_table = Table.FromColumns(List.Skip(all_columns, 9), List.Zip(List.FirstN(all_columns, 9)){0}) 

[MikeBlairTP]

Thanks for this, I'm trying to put this in my query

Original:

let
Source = Csv.Document(File.Contents("C:\temp\export.csv"),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type date}, {"Column16", type number}, {"Column17", type text}, {"Column18", Int64.Type}}),
all_columns = List.Buffer(Table.ToColumns(Source)), my_table = Table.FromColumns(List.Skip(all_columns, 9), List.Zip(List.FirstN(all_columns, 9)){0})

in
#"Changed Type"

How do I adapt 

all_columns = List.Buffer(Table.ToColumns(Source)), my_table = Table.FromColumns(List.Skip(all_columns, 9), List.Zip(List.FirstN(all_columns, 9)){0})

For the above query

[AlienSx · Accepted solution]

@MikeBlairTP give this a try

let
  Source = Csv.Document(
    File.Contents("C:\temp\export.csv"), 
    [Delimiter = ",", Columns = 18, Encoding = 1252, QuoteStyle = QuoteStyle.None]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Column1", type text}, 
      {"Column2", type text}, 
      {"Column3", type text}, 
      {"Column4", type text}, 
      {"Column5", type text}, 
      {"Column6", type text}, 
      {"Column7", type text}, 
      {"Column8", type text}, 
      {"Column9", type text}, 
      {"Column10", type text}, 
      {"Column11", type text}, 
      {"Column12", type text}, 
      {"Column13", type text}, 
      {"Column14", type text}, 
      {"Column15", type date}, 
      {"Column16", type number}, 
      {"Column17", type text}, 
      {"Column18", Int64.Type}
    }
  ), 
  all_columns = List.Buffer(Table.ToColumns(#"Changed Type")), 
  my_table = Table.FromColumns(List.Skip(all_columns, 9), List.Zip(List.FirstN(all_columns, 9)){0})
in
  my_table

[MikeBlairTP]

Thank you @AlienSx for the quick reply 🙂

I should have mentioned that I am doing this in Excel.

I have used the below but nothing happens

In your query where you reference my_table to I need to use the name of the table in excel?

let
  Source = Csv.Document(
    File.Contents(
      "C:\temp\export.csv"
    ), 
    [Delimiter = ",", Columns = 18, Encoding = 1252, QuoteStyle = QuoteStyle.None]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Column1", type text}, 
      {"Column2", type text}, 
      {"Column3", type text}, 
      {"Column4", type text}, 
      {"Column5", type text}, 
      {"Column6", type text}, 
      {"Column7", type text}, 
      {"Column8", type text}, 
      {"Column9", type text}, 
      {"Column10", type text}, 
      {"Column11", type text}, 
      {"Column12", type text}, 
      {"Column13", type text}, 
      {"Column14", type text}, 
      {"Column15", type date}, 
      {"Column16", type number}, 
      {"Column17", type text}, 
      {"Column18", Int64.Type}
    }
  ), 
  all_columns = List.Buffer(Table.ToColumns(#"Changed Type")), 
  my_table = Table.FromColumns(List.Skip(all_columns, 9), List.Zip(List.FirstN(all_columns, 9)){0})
in
  #"Changed Type"