Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MikeBlairTP
Regular Visitor

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

 

MikeBlairTP_1-1680714394940.png

 

 

1 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

View solution in original post

6 REPLIES 6
AlienSx
Super User
Super User

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

my_table

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

AlienSx
Super User
Super User

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}) 

 

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

@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

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"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors