Forum Discussion

Einomi's avatar
Einomi
Helper V
2 years ago
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...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • AlienSx's avatar
    AlienSx
    2 years ago

    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's avatar
Einomi
Helper V
2 years ago

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 MB

If you have any clue 😉 except the source step I did not change anything

ManuelBolz's avatar
ManuelBolz
Responsive Resident
2 years ago

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 Github

  • Einomi's avatar
    Einomi
    Helper V
    2 years ago

    ManuelBolz 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)