Forum Discussion

manyad's avatar
manyad
Regular Visitor
5 months ago
Solved

decompress, skip lines, delimit

All, I have a bunch of .gz files in a folder that I have to import into Power BI. After looking up online, i have built the following function. My files have the very first record which is file info ...
  • MarkLaf's avatar
    5 months ago

    Edit: actually, looking again at your issue + code (which is basically the same as mine), I think the main thing to change is to add a Columns field to your options record. When it's unspecified (as we are both doing in our M), I think it just looks at top row to determine column count (some number less than the ~20 you mentioned). So, I think the simple fix for you is:

     

    Imported = Csv.Document(Decompressed, [Columns=20, Delimiter="|", Encoding=1252]), // <-- added Columns=#

     

    Or, upstream, get the appropriate number of delimiters appended to first row such that it matches column count for primary data

     

    Original post for references:

    The following worked for me.

     

    For reference, I made two simple csvs of format:

     

    Zipped each with 7zip into gz. Note: original csvs in same folder but we are filtering them out per your procedure.

     

    The following M successfully unpacks these.

    let
        Source = Folder.Files( #"<FolderPath>" ),
        FilterGz = Table.SelectRows(Source, each [Extension] = ".gz"),
        AddGzParse = Table.AddColumn(
            FilterGz, "gz_tables", each 
            // steps for unpacking each gz - you can put these in a separate function if desired
            [ 
                gz = [Content],
                csv_binary = Binary.Decompress(gz, Compression.GZip), 
                csv_table = Csv.Document(csv_binary,null,"|"), 
                csv_table_trim = Table.RemoveLastN( Table.RemoveFirstN(csv_table,1), 1), 
                csv_table_promoted = Table.PromoteHeaders(csv_table_trim)
            ] [csv_table_promoted]
        ),
        // use our output from first row to automate column type and expansion
        // note, it's csv so all cols come in as text
        FirstTableType = Value.Type( List.First( AddGzParse[gz_tables] ) ),
        FixGzParseColType = Table.TransformColumns( 
            AddGzParse, 
            {{"gz_tables", each {_}{0}, FirstTableType }} 
        ),
        SelectMetaAndTables = Table.SelectColumns(
            FixGzParseColType,{"Name", "Date created", "gz_tables"}
        ),
        ExpandGz_tables = Table.ExpandTableColumn(
            SelectMetaAndTables, "gz_tables", 
            Type.TableSchema(FirstTableType)[Name] 
        )
    in
        ExpandGz_tables

     

    Output