Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.