Forum Discussion
Helper IHow can I keep data from a list of static column names plus two dynamic column names?
Hello!
I have no training in Power Query and have just been learning everything I need about it through reading other people's questions that seem similar to what I'm trying to do but this one I couldn't find.
I am pulling data from a collection of csv files (exports from JIRA tickets) on SharePoint into a new spreadsheet via Power Query. There are a ton of columns in the data that I don't need so I started out using "remove other columns" and listing the columns I wanted to keep. But sometimes there was more than one value in the same field in JIRA so the exported files have things like "Labels" and "Labels_7" to accomodate that.
The number of those "extra" columns might change every time we pull new data, from just the original copy of the column to several additional ones.
The current step in my query is:
= Table.SelectColumns(#"Changed Type",{"Issue key", "Summary", "Status", "Resolution", "Components", "Labels", "Parent", "Custom field (Story Points)", "Custom field (Epic/Theme)", "Custom field (Resolution Category)"})
But I want to dynamically be able to also have any additional "Components" and "Labels" columns that might be generated.
How can I specify a list of static column names plus those two dynamic column names?
Thank you muchly!
= Table.SelectColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"),each List.Contains({"Issue key", "Summary", "Status", "Resolution", "Components", "Labels", "Parent", "Custom field (Story Points)", "Custom field (Epic/Theme)", "Custom field (Resolution Category)"},_,(x,y)=>Text.StartsWith(y,x))))
Hi,
Thanks for the soluton wdx223_Daniel provided, and i want to offer some more information for user to refer to.
hello petrawiggin , you can create a new step named custom1 and input the following code.
=Table.SelectRows(Table.FromList(Table.ColumnNames(#"Changed Type")),each Text.Contains([Column1],"Label") or Text.Contains([Column1],"Component"))Then after the custom1 step create a new step named custom2
={"Issue key", "Summary", "Status", "Resolution","Parent", "Custom field (Story Points)", "Custom field (Epic/Theme)", "Custom field (Resolution Category)"}&Custom1[Column1]Then create a new step after custom2 named custom3
= Table.SelectColumns(#"Changed Type",Custom2)Output
When I add new column named label 7 component 7 , then will both diplay.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- wdx223_Daniel
Community Champion
= Table.SelectColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"),each List.Contains({"Issue key", "Summary", "Status", "Resolution", "Components", "Labels", "Parent", "Custom field (Story Points)", "Custom field (Epic/Theme)", "Custom field (Resolution Category)"},_,(x,y)=>Text.StartsWith(y,x))))
petrawigginAmazing! This did the trick!
Can you explain what that last part means so I can know how to use it better for other situations?- What does the ,_, do?
- What does (x,y)=> mean?
- Based on the Text.StartsWith, I'm guessing that the y is somehow "any of the category names I just listed" but I don't know how it's that, if it is.
- What is the x, which, based on the Text.StartsWith, seems to mean either "anything" or "_any number" but I'm not sure how, again.
Thank you so much for your solution and further help!
- wdx223_Daniel
_ is each item comes from Table.ColumnNames, ie the first parameter of List.Select
x is each item of the first parameter of List.Contains
y is the second parameter of List. Contains
(x,y)=>Text.StartsWith(y,x), is to define "What the mean of Contain"
Hi,
Thanks for the soluton wdx223_Daniel provided, and i want to offer some more information for user to refer to.
hello petrawiggin , you can create a new step named custom1 and input the following code.
=Table.SelectRows(Table.FromList(Table.ColumnNames(#"Changed Type")),each Text.Contains([Column1],"Label") or Text.Contains([Column1],"Component"))Then after the custom1 step create a new step named custom2
={"Issue key", "Summary", "Status", "Resolution","Parent", "Custom field (Story Points)", "Custom field (Epic/Theme)", "Custom field (Resolution Category)"}&Custom1[Column1]Then create a new step after custom2 named custom3
= Table.SelectColumns(#"Changed Type",Custom2)Output
When I add new column named label 7 component 7 , then will both diplay.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- petrawiggin
Hello.
This is a great solution for if I need to be careful to ONLY include the extra component or label columns!
Do you have a sense of whether this solution or the one-step one from @wdx223_Daniel would be more processing-heavy? Or if there is a way for me to tell?
(Since there is pretty much no way the other columns would have duplicates I might not need this many steps but if it's more efficient to run then I will definitely use it. And either way I will keep it for future reference because it is so handy!)
Am I correct in interpreting this solution as:- take all the column names from my data and make that into a list, then a one-column table then find all the names in that list that start with component or label
- make a list of the other columns I want to keep and add to it the names of the columns generated by step 1
- go back to the original table and keep only the columns from it that match the new list from step 2
- petrawiggin
Just checking back in. I wound up having to use this multi-step solution because there were other columns that started with other words that I didn't want. So thank you for this more specific solution as well!