admin管理员组文章数量:1401849
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:
Dataset 1 | Dataset 2 | Dataset 3 |
---|---|---|
DS1_Step1 | DS2_Step1 | DS3_Step1 |
DS1_Step2 | null | DS3_Step2 |
DS1_Step3 | null | null |
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:
Dataset 1 | Dataset 2 | Dataset 3 |
---|---|---|
DS1_Step1 | DS2_Step1 | DS3_Step1 |
DS1_Step2 | null | DS3_Step2 |
DS1_Step3 | null | null |
Say the user selects Dataset 1 and 2x Dataset 3, the output should look like:
Output |
---|
DS1_Step1 |
DS1_Step2 |
DS1_Step3 |
DS3_Step1 |
DS3_Step2 |
null |
DS3_Step1 |
DS3_Step2 |
null |
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:
Input |
---|
Query_1[#"Dataset 1"] |
Query_1[#"Dataset 3"] |
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?
Share Improve this question edited Mar 25 at 12:16 Tulip_Mama asked Mar 24 at 19:48 Tulip_MamaTulip_Mama 112 bronze badges 1- 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
Reset to default 1Just 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 yourQuery_1
table.- You may need to code this specifically: eg.
$A$1:$C$1
- You may need to code this specifically: eg.
- 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
本文标签: excelUsing ListCombine() with a list of table columnsStack Overflow
版权声明:本文标题:excel - Using List.Combine() with a list of table columns - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1744231013a2596333.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论