Forum Discussion
Delete Blank Columns in Nested Tables
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.
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
Thanks AlienSx but I must miss something, when I paste your code, I get a list of Column1, Column 2 and Column3 (or something similar I did not pay attention to the column numbers)
I want to delete the columns containing only null values from my nested tables that are in the "Content" column 😉
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.
I understood what the issue was, I assumed that some columns are only null because when I have previewed my nested tables, I was quite sure that that the preview is really the full data because my pdf contains maximum 2 pages and there is not much in them
So, I duplicated my query and expanded one of the tables and ideed there was one row at the bottom which was not null...
I'll test it and will come back to you, thanks all for your time