Get Data From File - how to delete empty cells

hi,

i am trying to get data from many files like these 2 attached.

Unfotunatelly, I get a lot of empty cells. i have tried to delete empty colonnes and rows but the Get Data funtion keeps on importing empty cells.

Can someone help me?

Thanks, Juan

SyndConta2019MSFT.xlsx55 KB

SyndConta2018MSFT.xlsx29 KB

excel

Formulas and Functions

SergeiBaklan

Dec 02, 2021

juan jimenez

You may Clean All in source files, if by Power Query you may apply function on second step

(Source as table) as table =>

let
    RemoveEmptyColumns = Table.SelectColumns(
        Source,
        List.Select(
            Table.ColumnNames(Source),
            each List.NonNullCount(Table.Column(Source,_)) <> 0
        )
    ),
    RemoveEmptyRows = Table.SelectRows(
        RemoveEmptyColumns,
        each not List.IsEmpty(List.RemoveMatchingItems( Record.FieldValues(_), {"", null} ) )
    )
in
    RemoveEmptyRows

3 Replies

SergeiBaklan
Diamond Contributor
Dec 02, 2021
juan jimenez

You may Clean All in source files, if by Power Query you may apply function on second step

(Source as table) as table => let RemoveEmptyColumns = Table.SelectColumns( Source, List.Select( Table.ColumnNames(Source), each List.NonNullCount(Table.Column(Source,_)) <> 0 ) ), RemoveEmptyRows = Table.SelectRows( RemoveEmptyColumns, each not List.IsEmpty(List.RemoveMatchingItems( Record.FieldValues(_), {"", null} ) ) ) in RemoveEmptyRows
- juan jimenez
  Iron Contributor
  Dec 02, 2021
  I wonder if it would be possible with the above files uploaded:
  1) to get in a column the name of the source document.
  2) to use the same query several times with the same files as from time to time the data is corrected.
  
  Thank you, juan
  - SergeiBaklan
    Diamond Contributor
    Dec 02, 2021
    juan jimenez
    
    1) it depends what your is your source. If From Folder you may keep file name. If From File you shall define filepath somehow. It could be parameter, whatever, you may add it as another column.
    
    2) That's how query works, you need only to refresh it.

Forum Discussion

Get Data From File - how to delete empty cells