Load multiple csv files, but different row counts until column heads

[Applicable88]

Hello,

I have multiple csv files that I want to load at once and combined with each other. These tables aren't typically formatted. I need to extract the date from the first second row as a additional column (to distinguish which csv I have) and afterwards delete empty rows and then put the columns into the head. There are sevaral empty rows until I reach the column line:

		Date	Timestamps
		01/01/2022
Column A		Column B		Column C		Column D		Column E		Column F
xxx		xxx		xxx		xxx		xxx		xxx

The problem I encounter now is that I have multiple csv files, where the column header sometimes has 4 rows that i need to delete to reach the top and sometimes more and less. Is there some command like delete only empty rows or until row =Column A? 

Thank you very much in advance. 

Best. 

[KNP · Accepted solution]

Hi @Applicable88,

So, for your second issue, here's a function I use regularly to combine multiple CSV files where the number of unwanted rows varies.

// fProcessFiles
let
  fProcessFiles = (myFile as binary) =>
    let
      CSV = Csv.Document(myFile, [Encoding=1252]),
      ConditionalBlankIndex = Table.AddColumn(CSV, "Custom", each if [Column2] = "" then 0 else 1),
      HeaderPosition = List.PositionOf(ConditionalBlankIndex[Custom], 1),
      RemoveRows = Table.Skip(CSV, HeaderPosition),
      PromotedHeaders = Table.PromoteHeaders(RemoveRows, [PromoteAllScalars = true])
    in
      PromotedHeaders
in
  fProcessFiles

Change the column reference '[Column2]' to a column that will be all blank until you hit a header.

Here's the other function I used in the main code.

// fNoHeader
let
  fProcessFiles = (myFile as binary) =>
    let
      CSV = Csv.Document(myFile, [Encoding=1252])
    in
      CSV
in
  fProcessFiles

Main code:

let
  Source = Folder.Files("H:\My Drive\Power BI\Community Solutions\Files"),
  #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "headers_filereference.csv")),
  #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Content"}),
  #"Invoked Custom Function" = Table.AddColumn(
    #"Removed Other Columns",
    "Query1",
    each fProcessFiles([Content])
  ),
  #"Invoked Custom Function1" = Table.AddColumn(
    #"Invoked Custom Function",
    "NoHeader",
    each fNoHeader([Content])
  ),
  #"Added Custom" = Table.AddColumn(
    #"Invoked Custom Function1",
    "Custom",
    each [NoHeader]{1}[Column3]
  ),
  #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom", {"Query1", "Custom"}),
  #"Expanded Query1" = Table.ExpandTableColumn(
    #"Removed Other Columns1",
    "Query1",
    {
      "Column A",
      "",
      "Column B",
      "_1",
      "Column C",
      "_2",
      "Column D",
      "_3",
      "Column E",
      "_4",
      "Column F",
      "_5"
    },
    {
      "Column A",
      "Column1",
      "Column B",
      "_1",
      "Column C",
      "_2",
      "Column D",
      "_3",
      "Column E",
      "_4",
      "Column F",
      "_5"
    }
  )
in
  #"Expanded Query1"

Basically...

• Connect to the folder with your files
• Remove all columns except your content
• Invoke both functions using the Add Column >> Invoke Custom Function
• Add a custom column referencing the detail you want to keep from each file
• Remove unwanted columns again
• Expand the column that has the table with filtered headers

Let me know if this requires further explanation.

[Applicable88]

@KNP very appreciate the thorough answer. Thanks!

[AlexisOlson]

The simplest approach would be to filter blanks from one of the columns but this requires that column to not have blanks in any rows you want to preserve.