Combine columns with corresponding names

[Anonymous]

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?

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler]

I believe you can do that with Table.SelectColumns

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

Thank you. But the column names are dynamic, so select columns does not really work. I'm able to filter them, but the next time i connect to a csv the names could have changed (excepted for the "condition" part). Is there maybe a way you can give me an example on how your second solution works? Hate to bother you with it, but i'm really cracking my brain on this 😛

EDIT: Thank you found out that this works (almost) perfectly. Going to find a way to perfect it a bit more. 

[Greg_Deckler]

OK, let me know if you get stuck, and post your solution here because I think it would help everyone. I've seen this issue come up a couple times now but everyone's situation is always a little different so the more example code out here the better!

[Anonymous · Accepted solution]

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
    step2

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

[Greg_Deckler]

Thanks for posting your solution! Very cool.