none
Excel Table Disappearing Formulas RRS feed

  • Question

  • Hi everyone, I'm trying to figure out if this is a bug in Excel or if it is functioning as designed. Here are my steps.

    1. Use "Power Query -> Get External Data -> From Database -> From SQL Server Database" to bring in data using this M code:

    let

        Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],

        Source = Sql.Database("SOLON-MAS", "DataWarehouse"),

        dbo_Vendors = Source{[Schema="dbo",Item="Vendors"]}[Data],

        RemovedOtherColumns = Table.SelectColumns(dbo_Vendors,{"Vend"}),

        FilteredRows = Table.SelectRows(RemovedOtherColumns, each Text.StartsWith([Vend], Parameters{0}[Value]))

    in

        FilteredRows

    2. The "Parameters" table in Excel is a one row table that looks like this: Parameter    Value Vendor         b

    3. I bring my Power Query query into Excel and it formats it as a table.

    4. I add a column called Column1 to this table using "Right Click -> Insert -> Table Column to the Right".

    5. I put a formula in Column1 of ="Tim".

    6. My table in Excel that is populated from my Power Query query now goes to row 343 and all rows have my ="Tim" formula like this:

    7. When I change the value in my "Parameters" table from "b" to "a" and click Refresh, I get a list of values that goes to row 434 instead of 343 (total coincidence that 434 and 343 are reflections of each other). The problem though is that my ="Tim" formula stops at row 342 yet oddly appears again on the last row like this:

    This seems like a bug to me. I would expect the ="Tim" formula to copy to all rows even though the number of rows keeps changing. I'm not expecting there to be any gaps. Any ideas?

    Tuesday, April 8, 2014 10:16 PM

Answers

  • Thanks for the report Tim. I can repro this on my machine and will file a bug. If it's not something we can fix in Power Query then we'll work with Excel to figure it out. Interestingly I can't repro this using only the classic data connectors in the Data tab so maybe there is something specific to Power Query.

    • Marked as answer by Tim Rodman Friday, April 11, 2014 3:06 PM
    Thursday, April 10, 2014 4:42 PM
    Moderator

All replies