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