I have a table where each column is a Data set with a series of actions needed to be taken under it. There are roughly 50 datasets, and this will expand in the future.

The user will select which Datasets they need, which may include duplicates, and the Query is supposed to append the steps they need to take.

Data looks a bit like this:

Query_1:

I have a table where each column is a Data set with a series of actions needed to be taken under it. There are roughly 50 datasets, and this will expand in the future.

The user will select which Datasets they need, which may include duplicates, and the Query is supposed to append the steps they need to take.

Data looks a bit like this:

Query_1:

Say the user selects Dataset 1 and 2x Dataset 3, the output should look like:

I can use List.Combine({Query_1[#"Dataset 1"],Query_1[#"Dataset 3"],Query_1[#"Dataset 3"]}) and get the correct output, but I need a dynamic list to put in the list area.

I created one from the input, and used a bunch of concatenating to give it the proper syntax, so now I have a list:

Query_2:

But when I use List.Combine(Query_2[Input]), I get an Expression Error. It says 'We cannot convert the value "Query_1[#"Dataset 1"]" to type List.

Can anyone tell me what I'm doing wrong here?

• excel
• list
• powerquery

• How is the user actually selecting the relevant data sets and the number to process? – Ron Rosenfeld Commented Mar 25 at 12:38

2 Answers 2

Just like the error message say, when you use List.Combine(Query_2[Input]), it tries to combine the text values rather than the actual lists. Instead you can try:

let
    // Your original table
    Query_1 = #table(
        {"Dataset 1", "Dataset 2", "Dataset 3"},
        {
            {"DS1_Step1", "DS2_Step1", "DS3_Step1"},
            {"DS1_Step2", null, "DS3_Step2"},
            {"DS1_Step3", null, null}
        }
    ),

    // Parameter table
    ParameterTable = #table(
        {"Dataset"},
        {
            {"Dataset 1"},
            {"Dataset 3"}
        }
    ),

    // Filter the original table based on the parameter table
    FilteredColumns = Table.SelectColumns(Query_1, ParameterTable[Dataset]),

    // Combine the steps from the filtered datasets
    CombinedSteps = List.Combine(Table.ToColumns(FilteredColumns))
in
    CombinedSteps

or you can do the last step in a single like:

FilterAndCombine = List.Combine(
    List.Transform(ParameterTable[Dataset], each Table.Column(Query_1, _)))

Seems to me the problem is how the user is going to select the data sets, since you do not indicate how that is actually done in your question. (The rest is relatively simple).

Here's one suggestion.

Someplace in your workbook

• Create a Table named, for example, Dataset_Chooser
• Make the table with enough rows to accomodate any reasonable number of choices
• Use Data Validation so the choices are limited to the Header range of your Query_1 table.
  • You may need to code this specifically: eg. $A$1:$C$1
• The user can then choose any of the datasets any number of times

For your M-Code:

let

//Read in your table
//May need to change the Name if your actual table name is different
    Source = Excel.CurrentWorkbook(){[Name="Query_1"]}[Content],

//Set the data types to type text
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Get the users choices as a list
    #"Choose" = List.Buffer(List.RemoveNulls(Excel.CurrentWorkbook(){[Name="Dataset_Chooser"]}[Content][DataSets])),

//Combine the results
    #"Result" = List.Accumulate(
            #"Choose",
            {},
            (s,c)=> s & Table.Column(#"Changed Type",c))

in
    #"Result"

Query_1

Dataset_Chooser

Results