How can we improve Power BI?

Allow to select custom data range on Excel sheet on import

When importing data from non-formatted Excel worksheets (not from tables or defined names) Power Query uses an UsedRange property of worksheet (as described there https://social.technet.microsoft.com/Forums/en-US/9d38f271-2cc6-4170-9a48-1e4f45f6f125/missed-column-from-xlsx-worksheet-on-import?forum=powerquery and there http://excel-inside.pro/blog/2017/05/23/excel-sheet-as-a-source-to-power-query-and-power-bi-a-pitfall-of-usedrange/). This behavior cause problems when there are first empty rows and/or columns on the sheet, which may or may not be imported depending on formats. It creates problems when working with a lot of files in folders.

As Power Query dont work with formats, there is no reason to rely on UsedRange.

Please add an option to
-- select top left cell of the cells range to be imported,
or
-- select range address to be imported,
or
-- start import from the A1 cell

24 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Maxim Zelensky shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Anonymous commented  ·   ·  Flag as inappropriate

        Honestly this *has* to go in the next release.
        The behavior of silently dropping empty columns before the data produced massive data inconsistencies in an actual work project.

        If the options are too hard to implement then the import of excel worksheets should always start from A1 to ensure consistency.

      Feedback and Knowledge Base

      Ready to get started?

      Try new features of Power BI today by signing up and learn more about our powerful suite of apps.