Forum Discussion

vincentakatoh's avatar
vincentakatoh
Helper IV
6 years ago

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-msft's avatar
    v-juanli-msft
    Community Support

    Hi vincentakatoh 

    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
    Maggie

     

    Community 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.