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

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.