Forum Discussion

JChris's avatar
JChris
Helper II
9 years ago
Solved

Folder source with CSVs, how to remove headers from each before merge?

I have the following strucutre:   Folder "Lorem Ipsum" Document "2017-07-13.csv" Document "2017-07-14.csv" Document "2017-07-17.csv" (and so on...)   I use this folder as source inside Po...
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    OK, my Transform query looks like this:

     

    let
        Source = Csv.Document(#"Sample File Parameter1",[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
    in
        #"Promoted Headers"

    This is the "Transform Sample File from "folder"" query.

     

    You should also have a function (fx) that looks like this:

     

    let
        Source = (#"Sample File Parameter1") => let
            Source = Csv.Document(#"Sample File Parameter1",[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
            #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
        in
            #"Promoted Headers"
    in
        Source

    Then you should have your main query that creates your data table that looks like this:

     

    let
        Source = Folder.Files("c:\temp\powerbi\csvs"),
        #"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from csvs", each #"Transform File from csvs"([Content])),
        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from csvs"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from csvs", Table.ColumnNames(#"Transform File from csvs"(#"Sample File"))),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"column1", Int64.Type}, {"column2", Int64.Type}, {"column3", Int64.Type}})
    in
        #"Changed Type"

    My CSV files are very simple, they look like this:

     

    one.csv

    column1,column2,column3
    1,10,100
    2,20,200
    3,30,300

     

    two.csv

    column1,column2,column3
    1,10,100
    2,20,200
    3,30,300

     

    three.csv

    column1,column2,column3
    1,10,100
    2,20,200
    3,30,300

     

    My data table looks like this:

    Source.Namecolumn1column2column3

    one.csv110100
    one.csv220200
    one.csv330300
    three.csv110100
    three.csv220200
    three.csv330300
    two.csv110100
    two.csv220200
    two.csv330300