locked
How to get Dropdown button for tables/queries in function? RRS feed

  • Question

  • When defining a function where tables and columns shall be passed as parameters, the dialog that appears in PowerBI is perfect, as it shows buttons which allows the user to select the objects without having to write any code. Unfortunately that doesn't happen for tables in PowerQuery (or at least I don't know how). Does anyone has an idea here here?:

    But in PBI it's fine:

    Thx!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Saturday, May 6, 2017 1:16 PM

Answers

  • Hi Imke. Your sample file works fine for me in Excel 2013, with our latest bits. Does the issue repro consistently for you? And are you by any chance running IE9 on the machine where you're seeing this?

    Thanks,

    Ehren

    Monday, May 8, 2017 8:10 PM

All replies

  • Hi Imke,

    Where did you take the first screen shot?
    I know Power Query in Excel and Power Query in Power BI Desktop, but Power Query as Power Query is new for me.

    A possible answer to your question might be that you have no tables defined, or would that be too simple?

    Some time ago I raised an issue which is a little bit related to yours: if you want to pass a record as parameter, then you won't get any field at all. Somehow that issue is marked "Delivered" whilst it won't be implemented in 2017H1.

    But let's regard this as a sidestep and keep the focus on your issue.

    Best Regards,
    Marcel

    Saturday, May 6, 2017 5:01 PM
  • That's odd. I see the dropdown in both Excel 2016 and the Power Query add-in for Excel 2010.

    I detest this dialog box with a passion because you can't enter a literal list, if you want to test your function without involving any particular table. Also, you can't select more than one column if your list is derived from multiple columns. 95% of the time, I have to modify the invoked function because of this poor design choice.

    Saturday, May 6, 2017 5:05 PM
  • Hi Marcel and Colin,

    thank you for coming back on this.

    I should have enclosed the file: https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!87153&parId=DE165DDF5D02DAFF!107&app=Excel

    It's in Excel 2013, so maybe it's a little bug in there (would be grateful if you could test the file on your 2016 and 2010 machines)

    @Marcel: Not sure what you mean with "no Tables defined", but there are queries in there who output tables. But no explicit specification of types ;-)

    @Colin: Can understand your view. Normally I'm too ignorant/lazy to specifically define a type at all, so not running into issues with that... ;-) But in this case I'm preparing some educational stuff where I wanted to make it as easy as possible for newbies - and demonstrate that it works the same in PBI and Excel. - Which it doesn't here.

    The specific problem here is, that the user has no chance to enter a correct value! He is forced to edit the code afterwards, because the entry into this field will be regarded as text (framed into "..") - that's the really annoying thing here.

    Thx again and cheers,


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Saturday, May 6, 2017 5:32 PM
  • What I meant was that there wouldn't be any tables (whether or not explicitly defined), but there are tables, so it was too simple indeed.

    Your file works fine with me in Excel 2016 and gives me a dropdown of tables when invoking the function.

    Didn't I see this file in a video on the Power BI Community forum?

    Cheers,

    Marcel

    Saturday, May 6, 2017 6:06 PM
  • Thx! So might be a bug in 2013 - good to know!

    Yes, that was part 1, part 2 with function is about to follow - might become a series ;-)


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Saturday, May 6, 2017 6:11 PM
  • File works fine for me too.
    Saturday, May 6, 2017 6:32 PM
  • Hi Imke. Your sample file works fine for me in Excel 2013, with our latest bits. Does the issue repro consistently for you? And are you by any chance running IE9 on the machine where you're seeing this?

    Thanks,

    Ehren

    Monday, May 8, 2017 8:10 PM
  • Ouch - now that I've updated to the latest version it works for me as well!

    Thx Ehren!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Monday, May 8, 2017 8:46 PM