Forum Discussion

Iron Contributor

Dec 02, 2021

Solved

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

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

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.