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 underneath it and not to the right off it

 

 

 

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

6 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hello, Anonymous 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})

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

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

    my_table

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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