Forum Discussion
Getting data from CSV files that have slightly different columns each week
Hi Anonymous
Try this. It's basically loading the files from the folder, transposing the tables, eliminating duplicates and transposing again
let Source = Folder.Files(FOLDER_WHERE_YOUR_DATA_IS), #"Added Custom" = Table.AddColumn(Source, "Custom", each Csv.Document([Content])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1"}), #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), #"Removed Duplicates" = Table.Distinct(#"Expanded Custom.1", {"Column1"}), #"Transposed Table" = Table.Transpose(#"Removed Duplicates"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job", type text}, {"WW31", Int64.Type}, {"WW32", Int64.Type}, {"WW33", Int64.Type}, {"WW34", Int64.Type}, {"WW35", Int64.Type}}) in #"Changed Type"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Anonymous
Try this. It's basically loading the files from the folder, transposing the tables, eliminating duplicates and transposing again
let
Source = Folder.Files(FOLDER_WHERE_YOUR_DATA_IS),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Csv.Document([Content])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Custom.1", {"Column1"}),
#"Transposed Table" = Table.Transpose(#"Removed Duplicates"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job", type text}, {"WW31", Int64.Type}, {"WW32", Int64.Type}, {"WW33", Int64.Type}, {"WW34", Int64.Type}, {"WW35", Int64.Type}})
in
#"Changed Type"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
But if I did that, wouldn't I need to go and change the query every week whenever I add a new file to my folder?
Anonymous
Nope. It should work as you add more files. Try it out, first with the files you showed and then add another one and see. The only thing you might have to change is the last step in the code above, to make it a bit more flexible when changing the data types. Something like (for the last step above):
= Table.TransformColumnTypes(#"Promoted Headers", List.RemoveFirstN(List.Zip({Table.ColumnNames(#"Promoted Headers"), List.Repeat({Int64.Type},Table.ColumnCount(#"Promoted Headers"))}),1))This changes the column types by extracting their names rather than hardcoding those as we did earlier
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Yes I see that! I have one more request/concern - although I showed only 3 Jobs, in reality there's hundreds, so is it possible to repeast the following line the same way?
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),