Forum Discussion

Einomi's avatar
Einomi
Helper V
2 years ago
Solved

Delete Blank Columns in Nested Tables

Hi, I have quickly searched on the forum but did not find what I need, I want to combine multiple PDF, but I prefer to write my own M code than using the UI, which is sometime limited and maybe my M...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • AlienSx's avatar
    AlienSx
    2 years ago

    Einomi my bad - wrong name after "in". I'll correct that soon. You may create blank query and place the following code in there (half of job is done by dufoq3 - check his query as well, it's also works)

    (tbl) => 
    [ReplaceBlankToNull = Table.TransformColumns(tbl, {}, each if _ = "" then null else _),
    profile = Table.Buffer(Table.Profile(ReplaceBlankToNull)),
    columns_to_delete = Table.SelectRows(profile, each [Count] = [NullCount])[Column],
    delete_columns = Table.RemoveColumns(ReplaceBlankToNull, columns_to_delete)][delete_columns]

    name the query as you wish. E.g. "fxRemoveBlankColumns". This is now a custom function that removes all null/blank columns from the table. In your "main" column make the following statement: 

    remove_blanks = Table.TransformColumns(ref_to_last_step, {"Content",  fxRemoveBlankColumns})

    Let us know if it worked. 

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Einomi, you can delete blank columns this way:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBIUcIpRSrAxMBIkNUEScgNsJQZIwq4ozNIBOwSCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    // You can probably delete this step.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    RemoveBlankColumns = Table.SelectColumns(ReplaceBlankToNull,  
        [ a = Table.ColumnNames(ReplaceBlankToNull),
          b = List.Select(a, (x)=> List.NonNullCount(Table.Column(ReplaceBlankToNull, x)) > 0)
        ][b] )
in
    RemoveBlankColumns
AlienSx's avatar
AlienSx
Super User
2 years ago

or 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBIUcIpRSrAxMBIkNUEScgNsJQZIwq4ozNIBOwSCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    // You can probably delete this step.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    profile = Table.Buffer(Table.Profile(ReplaceBlankToNull)),
    columns_to_delete = Table.SelectRows(profile, each [Count] = [NullCount])[Column],
    delete_columns = Table.RemoveColumns(ReplaceBlankToNull, columns_to_delete)
in
    delete_columns