Forum Discussion
Folder source with CSVs, how to remove headers from each before merge?
- 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 SourceThen 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,300two.csv
column1,column2,column3
1,10,100
2,20,200
3,30,300three.csv
column1,column2,column3
1,10,100
2,20,200
3,30,300My data table looks like this:
Source.Namecolumn1column2column3
one.csv 1 10 100 one.csv 2 20 200 one.csv 3 30 300 three.csv 1 10 100 three.csv 2 20 200 three.csv 3 30 300 two.csv 1 10 100 two.csv 2 20 200 two.csv 3 30 300
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
SourceThen 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.csv | 1 | 10 | 100 |
| one.csv | 2 | 20 | 200 |
| one.csv | 3 | 30 | 300 |
| three.csv | 1 | 10 | 100 |
| three.csv | 2 | 20 | 200 |
| three.csv | 3 | 30 | 300 |
| two.csv | 1 | 10 | 100 |
| two.csv | 2 | 20 | 200 |
| two.csv | 3 | 30 | 300 |