Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a table similar to the below
ID | Name | 1.abc | 1.def | NewColumn1 | 2.abc | 2.def | NewColumn2 |
1 | Jack | 2 | 2 | 2 | 1 | 3 | 2 |
1 | Jill | 2 | 4 | 3 | 1 | 1 | 1 |
I want to add NewColumn1 and NewColumn2 to basically look for all columns that has a 1 as the first character of the title and then average those columns.
Is this possible?
Thanks,
Solved! Go to Solution.
Hi @DizzyAy
I guess you have a variable huge number of columns that you cannot unpivot for some reason(s). I would recommend the following solution as per the attached sample file which works only as a measure (a calculated column using the same approach is not possible).
1.Sum =
SUMX (
'Table',
VAR T1 = CALCULATETABLE ( 'Table' )
VAR String1 = TOCSV ( T1, 1, ",", TRUE )
VAR Items1 = SUBSTITUTE ( String1, UNICHAR ( 10 ), "|" )
VAR T2 = SELECTCOLUMNS ( { ( 1, 2 ) }, "@Headers", PATHITEM ( Items1, 1 ), "@Details", PATHITEM ( Items1, 2 ) )
VAR T3 =
GENERATE (
T2,
VAR HeaderString = [@Headers]
VAR HeaderItems = SUBSTITUTE ( HeaderString, ",", "|" )
VAR DetailString = [@Details]
VAR DetailItems = SUBSTITUTE ( DetailString, ",", "|" )
VAR Length = PATHLENGTH ( HeaderItems )
VAR T4 = GENERATESERIES ( 1, Length, 1 )
RETURN
FILTER (
SELECTCOLUMNS (
T4,
"@Header", PATHITEM ( HeaderItems, [Value] ),
"@Detail", PATHITEM ( DetailItems, [Value] )
),
CONTAINSSTRING ( [@Header], "1" )
)
)
RETURN
SUMX ( T3, VALUE ( [@Detail] ) )
)
Hi @DizzyAy
I guess you have a variable huge number of columns that you cannot unpivot for some reason(s). I would recommend the following solution as per the attached sample file which works only as a measure (a calculated column using the same approach is not possible).
1.Sum =
SUMX (
'Table',
VAR T1 = CALCULATETABLE ( 'Table' )
VAR String1 = TOCSV ( T1, 1, ",", TRUE )
VAR Items1 = SUBSTITUTE ( String1, UNICHAR ( 10 ), "|" )
VAR T2 = SELECTCOLUMNS ( { ( 1, 2 ) }, "@Headers", PATHITEM ( Items1, 1 ), "@Details", PATHITEM ( Items1, 2 ) )
VAR T3 =
GENERATE (
T2,
VAR HeaderString = [@Headers]
VAR HeaderItems = SUBSTITUTE ( HeaderString, ",", "|" )
VAR DetailString = [@Details]
VAR DetailItems = SUBSTITUTE ( DetailString, ",", "|" )
VAR Length = PATHLENGTH ( HeaderItems )
VAR T4 = GENERATESERIES ( 1, Length, 1 )
RETURN
FILTER (
SELECTCOLUMNS (
T4,
"@Header", PATHITEM ( HeaderItems, [Value] ),
"@Detail", PATHITEM ( DetailItems, [Value] )
),
CONTAINSSTRING ( [@Header], "1" )
)
)
RETURN
SUMX ( T3, VALUE ( [@Detail] ) )
)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |