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.
Hello Einomi
If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.
When it comes to my solutions, I don't focus 100% on performance optimization. I always try to develop a compromise between performance and "code understanding". dufoq3´s code is probably more performant than my solution. Your "problem" is multiple file reads (unknown number of files). Therefore, I would generally advise you to choose a high-performance solution.
let
Source = Folder.Files(Ffiles),
FilterPDF = Table.SelectRows(Source, each ([Extension] = ".pdf")),
Content = Table.TransformColumns(FilterPDF, {"Content", each Pdf.Tables(_)}),
Columns = Table.SelectColumns(Content,{"Content"}),
Rows = Table.TransformColumns(Columns,{"Content", each Table.SelectRows(_, each [Kind] = "Page")}),
Combine = Table.TransformColumns(Rows, {"Content", each Table.Combine(_[Data])}),
Function = (table as table) as table =>
let
NonEmptyColumns = List.Select(Table.ColumnNames(table), each List.NonNullCount(Table.Column(table, _)) > 0),
Result = Table.SelectColumns(table, NonEmptyColumns)
in
Result,
ColumnsRemove = Table.TransformColumns(Combine, {"Content", each Function(_)}),
ColumnsList = List.Accumulate(ColumnsRemove[Content], {}, (state, current) => List.Union({state, Table.ColumnNames(current)})),
ColumnsNormalize = Table.TransformColumns(ColumnsRemove, {"Content", each Table.SelectColumns(_, List.Intersect({Table.ColumnNames(_), ColumnsList}))}),
CombinedTables = Table.Combine(ColumnsNormalize[Content]),
Headers = Table.PromoteHeaders(CombinedTables, [PromoteAllScalars=true])
in
Headers
Best regards from Germany
Manuel Bolz
🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github
Hi ManuelBolz
I would like to try your solution, but when I paste your M code into my Excel file, it just refreshing without stopping and I cannot see if this solution works or not, I have only 50 pdf files at the moment, each of them is less than 1 MBIf you have any clue 😉 except the source step I did not change anything
Hello Einomi
in my solution the source step is relatively important. Your example shows that you are using a Power Query parameter here.
I didn't fully understand your error with my solution. Here are a few comments/ideas:
- Make sure the "Ffiles" parameter points to the correct folder,
- First add only 2 - 4 PDF files to the folder to test the code,
- Make sure none of these files are open (PFD viewer) while testing my solution. A file handler is usually active here and blocks access to the file.If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.
Best regards from Germany
Manuel Bolz
🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on GithubManuelBolz thanks for the comment
- I use a path in my query, I replaced it with a parameter for confidentialy purposes in the forum
- No PDF file is open
I will test again your solution and post the error message I get (it is French so I need to change the language of PQ then send the screenshot)
