Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    8 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
        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!

7 Replies

    • Anonymous's avatar
      Anonymous
      Not 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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity 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.