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
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
  • 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"