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.
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
Hi dufoq3
thannks for your proposition, I have tried this and it does not seem to work, do I miss something ?
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(Table.Column(_, x)) > 0)
][b] )})
in
BlankCol
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