Get Data From File - how to delete empty cells

[juan jimenez]

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

[#2 · Accepted solution]

@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]

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

[#4]

@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.