PQ Custom Function dropdown

[lrobbo314]

I have a simple PQ query here.

(tbl as text) as table =>
let   
    Source = Excel.CurrentWorkbook(){[Name=tbl]}[Content],
    List = Table.AddColumn(Source, "Explode", each {1..[APP]}),
    Explode = Table.ExpandListColumn(List, "Explode"),
    RC = Table.RemoveColumns(Explode,{"Explode"}),
    ToOne = Table.TransformColumns(RC,{{"APP", each 1}})
in
    ToOne

When I invoke the custom function I need to manually type in the Table name. How would I make it to where it gives a dropdown of all the available tables in the workbook?

I have a list of all the tables in the workbook already, just need to know how to let the user choose from that list.

 

[sandy666]

maybe

Is there a way to create an "If Then" Group By in Power Query using parameter values?

I am trying to create a Power Query with a parameter that what would perform the Table.Group function based on the parameter value. For example, if the parameter value Group by Region= "Yes", then sum up the sales and profits for each region. If the parameter value does not equal "Group by...

www.mrexcel.com

 

[lrobbo314]

Thanks Sandy,

One of those links looks similar, but I'm not sure if it's exactly what I'm looking for. I'll have to dig into it a bit more.

I did get it to work, but it seems hacky and getting it to look like a legit PQ function is still a bit beyond me at this point. I'd also like to add another parameter that is dependent on the first parameter, i.e. get a list of columns based on the table selected in the first parameter. But we'll see.

Here's the working code so far. Thanks again for the links.

let
    fn = (tbl as text) as table =>
        let
            Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
            List = Table.AddColumn(Source, "Explode", each {1..[APP]}),
            Explode = Table.ExpandListColumn(List, "Explode"),
            RC = Table.RemoveColumns(Explode,{"Explode"}),
            ToOne = Table.TransformColumns(RC,{{"APP", each 1}})
        in
            ToOne,

    fnType = type function
        (
            Table_Name as (type text meta [Documentation.AllowedValues = tableList])
        ) as list
    in
    Value.ReplaceType(fn,fnType)

 

[sandy666]

hm, nested parameters? good luck

maybe this
Re: Are nested parameters supported ? as an example