Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Manipulate CSV and create headers

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 unde...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Anonymous 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
AlienSx's avatar
AlienSx
Super User
3 years ago

Anonymous 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
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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"