Forum Discussion
Combine columns with corresponding names
Hi there,
I'm building a report on a dataset of 1500 columns. The dataset contains 15 conditions of a 100 products (total of 1500 columns), per 15 seconds. Now i need to get the total per row, per condition, in a custom column. As i dont want to manullay select all 100 columns that go with a condition, i was wondering if it is possible to combine/sum/avg columns based on a part of their name.
e.g: (based on the picture below)
I want a custom column in which i sum all the values of every column with condition 1 in the name (column 2 + 5 as a new column), but I dont want to manually select 100 columns.
Is there a way within powerBI, preferably in power query?
Sorry, I should have been more clear. If you can select your column names using a wildcard with SelectColumns, then go with that. If not, you will want to get a list of your column names using ColumnNames. Then you can use List.Select to select items that meet your condition and then feed that list into Table.SelectColumns.
- Anonymous8 years ago
Solution:
- First load the data source in an empty query and leave it. It functions as a source/staging query.
- create a list and filter the rows that you want like this:
let Source = Source, #"step1" = Table.ColumnNames(Source), #"step2" = List.Select(#"step1", each Text.End(_, 21) = "Concurrent AudioCalls" or Text.End(_, 44) = "(PDH-CSV 4.0) (W. Europe Standard Time)(-60)") in step2This will give you a list with all columns that end with "concurrent audiocalls" and the correct timestamp (i have multiple timezones present).
create a new empty query and use the staging query as source. Select the columns by using the list:
let Source = Source, #"Removed Other Columns" = Table.SelectColumns(Source, ListConcurrentAudioCalls) in #"Removed Other Columns"And now you can go on as usual :)
Thanks to Smoupre for giving me this solution!
7 Replies
- Greg_Deckler
Community Champion
I believe you can do that with Table.SelectColumns
- AnonymousNot applicable
Hi Smoupre,
Thank you for your response, but I'm not sure how the function Table.SelectColumns will fit here. In the describtion online (https://msdn.microsoft.com/en-us/library/mt260828.aspx) I'm not able to find how to apply it for my situation. Maybe you can assist or give me hint? Thank you in advance- Greg_Deckler
Community Champion
Sorry, I should have been more clear. If you can select your column names using a wildcard with SelectColumns, then go with that. If not, you will want to get a list of your column names using ColumnNames. Then you can use List.Select to select items that meet your condition and then feed that list into Table.SelectColumns.