Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
petrawiggin
Frequent Visitor

How 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! 

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

= 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))))

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1714615990261.png

When I add new column named label 7 component 7 , then will both diplay.

vxinruzhumsft_1-1714616110960.png

 

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.

View solution in original post

6 REPLIES 6
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1714615990261.png

When I add new column named label 7 component 7 , then will both diplay.

vxinruzhumsft_1-1714616110960.png

 

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.

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!

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:

  1. 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
  2. make a list of the other columns I want to keep and add to it the names of the columns generated by step 1
  3. go back to the original table and keep only the columns from it that match the new list from step 2
wdx223_Daniel
Super User
Super User

= 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))))

Amazing! This did the trick! 
Can you explain what that last part means so I can know how to use it better for other situations?

  1. What does the ,_, do?
  2. What does (x,y)=> mean?
    1. 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.
    2. 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!

_ 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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors