Forum Discussion
Missing Columns Get Data from Folder
Hi,
My data soure are .log files in a specific folder. When getting data from folder or file, some columns are missing. There are 26 columns but only 13 columns appears.
a) When I use Get Data from csv/txt (ie. single file), I can work around this issue by using advance editor and changing the number of columns from 13 to 25. Below is an example after changing "Columns=26" (was "Columns=13").
let
Source = Csv.Document(File.Contents("C:\Users\Desktop\Data\A_3D0067_01.log"),[Delimiter=" ", Columns=26, Encoding=1252]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type time}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}})
in
#"Changed Type"b) Help needed: When Get Data from Folder, I cant find the equivalent, where I can change the number of columns? Can anyone help? How can I change, so Power BI reads all 26 columns.
let
Source = Folder.Files("C:\Users\Desktop\Data"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Data (2)", each #"Transform File from Data (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Data (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Data (2)", Table.ColumnNames(#"Transform File from Data (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type time}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}})
in
#"Changed Type"1 Reply
- v-juanli-msftCommunity Support
As tested, when i get data from folder, i can combine data in the Edit queries,
Some files are nested as a "table" cell, we can expand it to show all data.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.