Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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!
I believe you can do that with Table.SelectColumns
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.
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!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |