Forum Discussion
Creating a Column/Measure for List Length
Depends on what you mean by "length". I am assuming you are referring to how many items are in the list. If that is the case, use my special trickiness for this assuming that your items are separated by something like a comma or something:
Column = LEN([List]) - LEN(SUBSTITUTE(List[List],",","")) + 1
Ok, I believe I figured out your equation, though it won't do what I need. If I understand correctly, your equation assumes the Cell contents are a string. It counts the number of total characters, counts the number of non-comma characters, then takes the difference and adds 1; essentially it counts commas+1 (or a delimiter of your choosing) to get the list elements.
Unfortunately, this column does not store the data as strings formatted as a list; they are the 'List' type. When looking at the data in "Query Editor", the field contents show up as a yellow "List"; you can select the cell, and a mini-table appears at the bottom showing the list contents as their own column.
My problem is that I cannot find any documentation on how to interact with this object. Both 'List' and 'Record' appear in my imported data set, but Power BI does not even reference these in their official Data Type documentation even though it clearly is handling them uniquely!
Calculating a simple length here doesn't seem too crazy. There is a function called COUNTROWS which counts the rows in a table. As a 'List' shows up as a mini-table, I would think that executing COUNTROWS on this column should do what I want, but this gives me the error "The COUNTROWS function expects a table expression for argument '1', but a string or numeric expression was used.".
There has to be a way to do this...