Forum Discussion

Antman75's avatar
Antman75
Copper Contributor
Feb 07, 2025

Help creating a matrix table

Good day wonderful people, I am trying to create a matrix table that shows correlations between types of issues people may be worrying about. The end result is to enable me to bring it into Power BI...
BI & Data Analysis
excel
Formulas and Functions
power bi
djclements's avatar
djclements
Silver Contributor
Feb 14, 2025

If you're interested in a dynamic array option, try the following:

=LET(
   cId, A2:A12,
   txt, BYROW(B2:E12, LAMBDA(r, LET(
        a, TOCOL(r, 1),
        n, ROWS(a),
        v, IF(ISERROR(n), "^", IF(n = 1, a & "^", TOCOL(IFS(SEQUENCE(, n) > SEQUENCE(n), a & "^" & TOROW(a)), 2))),
        TEXTJOIN("|", 0, ROWS(v), v, "")))),
   arr, TEXTBEFORE(TEXTAFTER(txt, "|", SEQUENCE(, MAX(--TEXTBEFORE(txt, "|")))), "|"),
   vec, TOCOL(arr, 2),
   HSTACK(TOCOL(IFS(NOT(ISERROR(arr)), cId), 2), TEXTBEFORE(vec, "^"), TEXTAFTER(vec, "^"))
)

Please note, a, TOCOL(r, 1), assumes the blank cells shown in your screenshot are truly blank. If that's not the case with your exported data, and they actually contain zero-length strings (""), change this line to:

a, TOCOL(IFS(r<>"", r), 2),

The attached file also contains a couple of options for counting the occurrence of pairs afterwards using either GROUPBY or PIVOTBY...

Resources