locked
import a sheet without importing hidden rows RRS feed

  • Question

  • Can Power Query (using excel) import an excel worksheet "list.xlsx" without importing the rows that are saved as hidden (or is Power Query able to delete the hidden rows after importing because it can detect hidden-ness)  in the original sheet?  There are no tables list.xlsx.  Thank you.

    PK

    Tuesday, November 7, 2017 11:37 PM

Answers

  • Hey,

    The Excel file connector for Power Query does not read any of the formatting options entered through Excel at the cell level. This means that you only get the value inside of a cell, but not the formatting (like color, font or other metadata like visibility).

    If you could programmatically detect the rows that you need to filter out through some sort of logic, then you could implement that logic inside of POwer Query and just end with the rows that you need.


    Wednesday, November 8, 2017 4:11 AM

All replies

  • Hey,

    The Excel file connector for Power Query does not read any of the formatting options entered through Excel at the cell level. This means that you only get the value inside of a cell, but not the formatting (like color, font or other metadata like visibility).

    If you could programmatically detect the rows that you need to filter out through some sort of logic, then you could implement that logic inside of POwer Query and just end with the rows that you need.


    Wednesday, November 8, 2017 4:11 AM
  • "The Excel file connector for Power Query does not read any of the formatting options entered through Excel at the cell level."

    Actually, Power Query does interpret the numeric formatting in a cell, in lieu of any specific data type metadata (even though it insists on interpreting a date as a datetime value). However, your post addresses the issues and proposes what might be the only possible solution.

    Wednesday, November 8, 2017 6:18 PM