Forum Discussion
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...
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 do not see your data, but maybe you don't have blank values as null. Try this:
let
Source = Folder.Files(Ffiles),
FilterPDF = Table.SelectRows(Source, each ([Extension] = ".pdf")),
BinaryToTables = Table.TransformColumns(FilterPDF, {"Content", each Pdf.Tables(_)}),
KeepContent = Table.SelectColumns(BinaryToTables,{"Content"}),
KeepPages = Table.TransformColumns(KeepContent,{"Content", each Table.SelectRows(_, each [Kind] = "Page")}),
CombineData = Table.TransformColumns(KeepPages, {"Content", each Table.Combine(_[Data])}),
SkipInvoice = Table.TransformColumns(CombineData,{"Content", each Table.Skip(_, each [Column1] <> "Invoice")}),
ColumnCount = Table.AddColumn(SkipInvoice,"Columns", each List.Count (Table.ColumnNames([Content]))),
SortRows = Table.Sort(ColumnCount,{{"Columns", Order.Ascending}}),
BlankCol = Table.TransformColumns(SortRows, {"Content", each Table.SelectColumns(_,
[ a = Table.ColumnNames(_),
b = List.Select(a, (x)=> List.NonNullCount(List.Select(Table.Column(_, x), (y)=> Text.Trim(y) <> "")) > 0)
][b] )})
in
BlankCol
