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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

 

 2018-02-20 13_16_38-sample - Excel.png

 

Is there a way within powerBI, preferably in power query?

 

 

2 ACCEPTED SOLUTIONS

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Anonymous
Not applicable

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!

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

I believe you can do that with Table.SelectColumns


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!

Thanks for posting your solution! Very cool.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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