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
It sounds like you are not using a Folder query, which is what you should be using. Let me know if I am incorrect.
- JChris9 years agoHelper III am using Folder query.
- Greg_Deckler9 years agoCommunity Champion
OK, well then it sounds like you aren't doing a Promoted Headers in your sample transform step. Go into Query Editor and look for the folder:
Transform File from "folder"
Within that folder should be a query called something like "Transform Sample File from "folder". Make sure that query does a "Promoted Headers" step.
- JChris9 years agoHelper IISo, I went to the place you said and added the "Promoted Headers" to the sample file that was there. I applied it and refreshed and now all data is null inside my query...