Forum Discussion
Power Bi Challenge - Round 2
tamerj1 Hey, here's a version that doesn't require hardcoding and you can theoretically add as many pre-columns as you want. Basically Measure 2 but with a dynamic finding of the number of commas before the first value column.
Measure 4 = VAR __Text = SUBSTITUTE( TOCSV( 'T_MEASURES', , ",", 1 ), UNICHAR(10), "|" ) VAR __1 = PATHITEM( __Text, 1 ) VAR __FirstColon = FIND(":", __1) VAR __Left = LEFT( __1, __FirstColon ) VAR __Commas = LEN( __Left ) - LEN( SUBSTITUTE( __Left, ",", "" ) ) VAR __2 = PATHITEM( __Text, 2 ) VAR __3 = SUBSTITUTE( __2, ",", "|", __Commas ) VAR __4 = PATHITEM( __3, 2 ) VAR __Path = SUBSTITUTE( __4, ",", "|" ) VAR __Table = ADDCOLUMNS( GENERATESERIES(1, 144, 1), "__Value", PATHITEM( __Path, [Value]) + 0 ) VAR __Result = AVERAGEX( __Table, [__Value] ) RETURN __Resulttamerj1 OK, one "final" version. This one *should* work with multiple rows of sensor data on the same day and doesn't assume basically anything in terms of how many columns of data, just that the first column of data that the name includes a ":" and that after that the data columns are sequential with no non-data columns mixed in.
I don't have the original data file so I can't actually test it with multiple data on the same day (although that doesn't make sense in this scenario it might make sense in other similar circumstances or scenarios so I wanted to try doing it).
Measure 5 = // Deal with the header to figure out what columns need to be removed VAR __TableWHeaders = SUBSTITUTE( TOCSV( 'T_MEASURES', , ",", 1 ), UNICHAR(10), "|" ) // TOCSV with Headers VAR __Headers = PATHITEM( __TableWHeaders, 1 ) //headers VAR __FirstColon = FIND(":", __Headers) // find the first colon, which should be the first column of data VAR __Left = LEFT( __Headers, __FirstColon ) // grab everything to the left of the first colon VAR __Commas = LEN( __Left ) - LEN( SUBSTITUTE( __Left, ",", "" ) ) // figure out how many commas (columns) are in the "pre-columns" before the data // Now deal with the data which might have multiple rows VAR __TableWOHeaders = SUBSTITUTE( TOCSV( 'T_MEASURES', , ",", 0 ), UNICHAR(10), "|" ) // TOCSV without Headers VAR __Count = COUNTROWS('T_MEASURES') // number of rows in table VAR __Data = // Generate a table with a column that strips out the pre-columns ADDCOLUMNS( GENERATESERIES(1, __Count, 1), "__Data", VAR __Text = PATHITEM( __TableWOHeaders, [Value]) VAR __Path = SUBSTITUTE( __Text, ",", "|", __Commas ) VAR __Result = PATHITEM( __Path, 2 ) RETURN __Result ) VAR __DataText = CONCATENATEX( __Data, [__Data], "|" ) // convert the table back into a text string with each row as a path item VAR __Path = SUBSTITUTE( __DataText, ",", "|" ) // convert all columns to path items VAR __DataColumns = LEN( __DataText) - LEN( SUBSTITUTE( __DataText, ",", "" ) ) // figure out how many columns of data we are dealing with VAR __Table = // Convert the path into a table ADDCOLUMNS( GENERATESERIES(1, __DataColumns, 1), "__Value", PATHITEM( __Path, [Value]) + 0 ) VAR __Result = AVERAGEX( __Table, [__Value] ) // run an aggregation against the table RETURN __ResultThe other obvious hard-coded solution (manual unpivoting -- shown below) works too. It has more overhead but performance is still acceptable. The overhead is largely fixed and performance is barely affected by changing from one month of data to five months of data, unlike the text-parsing solutions whose timing scales roughly linearly with the number of months of data selected and quickly become impractical for larger amounts of data.
Testing twenty cold cache trials on the full set of data (still only 876 rows):
My simple solution above:A more general unpivot solution (below):
tamerj1's solution from above:
Avg Temp (Unpivot) = VAR __Unpivot__ = UNION ( SELECTCOLUMNS ( T, "@Time", "00:00", "@Temp", T[00:00] ), SELECTCOLUMNS ( T, "@Time", "00:10", "@Temp", T[00:10] ), SELECTCOLUMNS ( T, "@Time", "00:20", "@Temp", T[00:20] ), SELECTCOLUMNS ( T, "@Time", "00:30", "@Temp", T[00:30] ), SELECTCOLUMNS ( T, "@Time", "00:40", "@Temp", T[00:40] ), SELECTCOLUMNS ( T, "@Time", "00:50", "@Temp", T[00:50] ), SELECTCOLUMNS ( T, "@Time", "01:00", "@Temp", T[01:00] ), SELECTCOLUMNS ( T, "@Time", "01:10", "@Temp", T[01:10] ), SELECTCOLUMNS ( T, "@Time", "01:20", "@Temp", T[01:20] ), SELECTCOLUMNS ( T, "@Time", "01:30", "@Temp", T[01:30] ), SELECTCOLUMNS ( T, "@Time", "01:40", "@Temp", T[01:40] ), SELECTCOLUMNS ( T, "@Time", "01:50", "@Temp", T[01:50] ), SELECTCOLUMNS ( T, "@Time", "02:00", "@Temp", T[02:00] ), SELECTCOLUMNS ( T, "@Time", "02:10", "@Temp", T[02:10] ), SELECTCOLUMNS ( T, "@Time", "02:20", "@Temp", T[02:20] ), SELECTCOLUMNS ( T, "@Time", "02:30", "@Temp", T[02:30] ), SELECTCOLUMNS ( T, "@Time", "02:40", "@Temp", T[02:40] ), SELECTCOLUMNS ( T, "@Time", "02:50", "@Temp", T[02:50] ), SELECTCOLUMNS ( T, "@Time", "03:00", "@Temp", T[03:00] ), SELECTCOLUMNS ( T, "@Time", "03:10", "@Temp", T[03:10] ), SELECTCOLUMNS ( T, "@Time", "03:20", "@Temp", T[03:20] ), SELECTCOLUMNS ( T, "@Time", "03:30", "@Temp", T[03:30] ), SELECTCOLUMNS ( T, "@Time", "03:40", "@Temp", T[03:40] ), SELECTCOLUMNS ( T, "@Time", "03:50", "@Temp", T[03:50] ), SELECTCOLUMNS ( T, "@Time", "04:00", "@Temp", T[04:00] ), SELECTCOLUMNS ( T, "@Time", "04:10", "@Temp", T[04:10] ), SELECTCOLUMNS ( T, "@Time", "04:20", "@Temp", T[04:20] ), SELECTCOLUMNS ( T, "@Time", "04:30", "@Temp", T[04:30] ), SELECTCOLUMNS ( T, "@Time", "04:40", "@Temp", T[04:40] ), SELECTCOLUMNS ( T, "@Time", "04:50", "@Temp", T[04:50] ), SELECTCOLUMNS ( T, "@Time", "05:00", "@Temp", T[05:00] ), SELECTCOLUMNS ( T, "@Time", "05:10", "@Temp", T[05:10] ), SELECTCOLUMNS ( T, "@Time", "05:20", "@Temp", T[05:20] ), SELECTCOLUMNS ( T, "@Time", "05:30", "@Temp", T[05:30] ), SELECTCOLUMNS ( T, "@Time", "05:40", "@Temp", T[05:40] ), SELECTCOLUMNS ( T, "@Time", "05:50", "@Temp", T[05:50] ), SELECTCOLUMNS ( T, "@Time", "06:00", "@Temp", T[06:00] ), SELECTCOLUMNS ( T, "@Time", "06:10", "@Temp", T[06:10] ), SELECTCOLUMNS ( T, "@Time", "06:20", "@Temp", T[06:20] ), SELECTCOLUMNS ( T, "@Time", "06:30", "@Temp", T[06:30] ), SELECTCOLUMNS ( T, "@Time", "06:40", "@Temp", T[06:40] ), SELECTCOLUMNS ( T, "@Time", "06:50", "@Temp", T[06:50] ), SELECTCOLUMNS ( T, "@Time", "07:00", "@Temp", T[07:00] ), SELECTCOLUMNS ( T, "@Time", "07:10", "@Temp", T[07:10] ), SELECTCOLUMNS ( T, "@Time", "07:20", "@Temp", T[07:20] ), SELECTCOLUMNS ( T, "@Time", "07:30", "@Temp", T[07:30] ), SELECTCOLUMNS ( T, "@Time", "07:40", "@Temp", T[07:40] ), SELECTCOLUMNS ( T, "@Time", "07:50", "@Temp", T[07:50] ), SELECTCOLUMNS ( T, "@Time", "08:00", "@Temp", T[08:00] ), SELECTCOLUMNS ( T, "@Time", "08:10", "@Temp", T[08:10] ), SELECTCOLUMNS ( T, "@Time", "08:20", "@Temp", T[08:20] ), SELECTCOLUMNS ( T, "@Time", "08:30", "@Temp", T[08:30] ), SELECTCOLUMNS ( T, "@Time", "08:40", "@Temp", T[08:40] ), SELECTCOLUMNS ( T, "@Time", "08:50", "@Temp", T[08:50] ), SELECTCOLUMNS ( T, "@Time", "09:00", "@Temp", T[09:00] ), SELECTCOLUMNS ( T, "@Time", "09:10", "@Temp", T[09:10] ), SELECTCOLUMNS ( T, "@Time", "09:20", "@Temp", T[09:20] ), SELECTCOLUMNS ( T, "@Time", "09:30", "@Temp", T[09:30] ), SELECTCOLUMNS ( T, "@Time", "09:40", "@Temp", T[09:40] ), SELECTCOLUMNS ( T, "@Time", "09:50", "@Temp", T[09:50] ), SELECTCOLUMNS ( T, "@Time", "10:00", "@Temp", T[10:00] ), SELECTCOLUMNS ( T, "@Time", "10:10", "@Temp", T[10:10] ), SELECTCOLUMNS ( T, "@Time", "10:20", "@Temp", T[10:20] ), SELECTCOLUMNS ( T, "@Time", "10:30", "@Temp", T[10:30] ), SELECTCOLUMNS ( T, "@Time", "10:40", "@Temp", T[10:40] ), SELECTCOLUMNS ( T, "@Time", "10:50", "@Temp", T[10:50] ), SELECTCOLUMNS ( T, "@Time", "11:00", "@Temp", T[11:00] ), SELECTCOLUMNS ( T, "@Time", "11:10", "@Temp", T[11:10] ), SELECTCOLUMNS ( T, "@Time", "11:20", "@Temp", T[11:20] ), SELECTCOLUMNS ( T, "@Time", "11:30", "@Temp", T[11:30] ), SELECTCOLUMNS ( T, "@Time", "11:40", "@Temp", T[11:40] ), SELECTCOLUMNS ( T, "@Time", "11:50", "@Temp", T[11:50] ), SELECTCOLUMNS ( T, "@Time", "12:00", "@Temp", T[12:00] ), SELECTCOLUMNS ( T, "@Time", "12:10", "@Temp", T[12:10] ), SELECTCOLUMNS ( T, "@Time", "12:20", "@Temp", T[12:20] ), SELECTCOLUMNS ( T, "@Time", "12:30", "@Temp", T[12:30] ), SELECTCOLUMNS ( T, "@Time", "12:40", "@Temp", T[12:40] ), SELECTCOLUMNS ( T, "@Time", "12:50", "@Temp", T[12:50] ), SELECTCOLUMNS ( T, "@Time", "13:00", "@Temp", T[13:00] ), SELECTCOLUMNS ( T, "@Time", "13:10", "@Temp", T[13:10] ), SELECTCOLUMNS ( T, "@Time", "13:20", "@Temp", T[13:20] ), SELECTCOLUMNS ( T, "@Time", "13:30", "@Temp", T[13:30] ), SELECTCOLUMNS ( T, "@Time", "13:40", "@Temp", T[13:40] ), SELECTCOLUMNS ( T, "@Time", "13:50", "@Temp", T[13:50] ), SELECTCOLUMNS ( T, "@Time", "14:00", "@Temp", T[14:00] ), SELECTCOLUMNS ( T, "@Time", "14:10", "@Temp", T[14:10] ), SELECTCOLUMNS ( T, "@Time", "14:20", "@Temp", T[14:20] ), SELECTCOLUMNS ( T, "@Time", "14:30", "@Temp", T[14:30] ), SELECTCOLUMNS ( T, "@Time", "14:40", "@Temp", T[14:40] ), SELECTCOLUMNS ( T, "@Time", "14:50", "@Temp", T[14:50] ), SELECTCOLUMNS ( T, "@Time", "15:00", "@Temp", T[15:00] ), SELECTCOLUMNS ( T, "@Time", "15:10", "@Temp", T[15:10] ), SELECTCOLUMNS ( T, "@Time", "15:20", "@Temp", T[15:20] ), SELECTCOLUMNS ( T, "@Time", "15:30", "@Temp", T[15:30] ), SELECTCOLUMNS ( T, "@Time", "15:40", "@Temp", T[15:40] ), SELECTCOLUMNS ( T, "@Time", "15:50", "@Temp", T[15:50] ), SELECTCOLUMNS ( T, "@Time", "16:00", "@Temp", T[16:00] ), SELECTCOLUMNS ( T, "@Time", "16:10", "@Temp", T[16:10] ), SELECTCOLUMNS ( T, "@Time", "16:20", "@Temp", T[16:20] ), SELECTCOLUMNS ( T, "@Time", "16:30", "@Temp", T[16:30] ), SELECTCOLUMNS ( T, "@Time", "16:40", "@Temp", T[16:40] ), SELECTCOLUMNS ( T, "@Time", "16:50", "@Temp", T[16:50] ), SELECTCOLUMNS ( T, "@Time", "17:00", "@Temp", T[17:00] ), SELECTCOLUMNS ( T, "@Time", "17:10", "@Temp", T[17:10] ), SELECTCOLUMNS ( T, "@Time", "17:20", "@Temp", T[17:20] ), SELECTCOLUMNS ( T, "@Time", "17:30", "@Temp", T[17:30] ), SELECTCOLUMNS ( T, "@Time", "17:40", "@Temp", T[17:40] ), SELECTCOLUMNS ( T, "@Time", "17:50", "@Temp", T[17:50] ), SELECTCOLUMNS ( T, "@Time", "18:00", "@Temp", T[18:00] ), SELECTCOLUMNS ( T, "@Time", "18:10", "@Temp", T[18:10] ), SELECTCOLUMNS ( T, "@Time", "18:20", "@Temp", T[18:20] ), SELECTCOLUMNS ( T, "@Time", "18:30", "@Temp", T[18:30] ), SELECTCOLUMNS ( T, "@Time", "18:40", "@Temp", T[18:40] ), SELECTCOLUMNS ( T, "@Time", "18:50", "@Temp", T[18:50] ), SELECTCOLUMNS ( T, "@Time", "19:00", "@Temp", T[19:00] ), SELECTCOLUMNS ( T, "@Time", "19:10", "@Temp", T[19:10] ), SELECTCOLUMNS ( T, "@Time", "19:20", "@Temp", T[19:20] ), SELECTCOLUMNS ( T, "@Time", "19:30", "@Temp", T[19:30] ), SELECTCOLUMNS ( T, "@Time", "19:40", "@Temp", T[19:40] ), SELECTCOLUMNS ( T, "@Time", "19:50", "@Temp", T[19:50] ), SELECTCOLUMNS ( T, "@Time", "20:00", "@Temp", T[20:00] ), SELECTCOLUMNS ( T, "@Time", "20:10", "@Temp", T[20:10] ), SELECTCOLUMNS ( T, "@Time", "20:20", "@Temp", T[20:20] ), SELECTCOLUMNS ( T, "@Time", "20:30", "@Temp", T[20:30] ), SELECTCOLUMNS ( T, "@Time", "20:40", "@Temp", T[20:40] ), SELECTCOLUMNS ( T, "@Time", "20:50", "@Temp", T[20:50] ), SELECTCOLUMNS ( T, "@Time", "21:00", "@Temp", T[21:00] ), SELECTCOLUMNS ( T, "@Time", "21:10", "@Temp", T[21:10] ), SELECTCOLUMNS ( T, "@Time", "21:20", "@Temp", T[21:20] ), SELECTCOLUMNS ( T, "@Time", "21:30", "@Temp", T[21:30] ), SELECTCOLUMNS ( T, "@Time", "21:40", "@Temp", T[21:40] ), SELECTCOLUMNS ( T, "@Time", "21:50", "@Temp", T[21:50] ), SELECTCOLUMNS ( T, "@Time", "22:00", "@Temp", T[22:00] ), SELECTCOLUMNS ( T, "@Time", "22:10", "@Temp", T[22:10] ), SELECTCOLUMNS ( T, "@Time", "22:20", "@Temp", T[22:20] ), SELECTCOLUMNS ( T, "@Time", "22:30", "@Temp", T[22:30] ), SELECTCOLUMNS ( T, "@Time", "22:40", "@Temp", T[22:40] ), SELECTCOLUMNS ( T, "@Time", "22:50", "@Temp", T[22:50] ), SELECTCOLUMNS ( T, "@Time", "23:00", "@Temp", T[23:00] ), SELECTCOLUMNS ( T, "@Time", "23:10", "@Temp", T[23:10] ), SELECTCOLUMNS ( T, "@Time", "23:20", "@Temp", T[23:20] ), SELECTCOLUMNS ( T, "@Time", "23:30", "@Temp", T[23:30] ), SELECTCOLUMNS ( T, "@Time", "23:40", "@Temp", T[23:40] ), SELECTCOLUMNS ( T, "@Time", "23:50", "@Temp", T[23:50] ) ) VAR __Result = AVERAGEX ( __Unpivot__, [@Temp] ) RETURN __ResultAnonymous I thought about an alternate solution instead of DAX.
@tamerj1 I have though about an alternate approach to solve the above challenge.
As in the original fole we do not have the option to model the data then we publish it and we set the maximum amount of refresh's possible(depending on licence)
We open a new .pbix file and then through XMLA endpoint we bring the published data in the new .pbix file. Where we unpivot all the columns with the values so we get rid of the multi column aggregations. From that on it pretty simple to build the visuals.
I have cheated a bit as it has not been solved with DAX but it works.I am also attaching a video link of the steps how to do it.
True, but I was hoping not to have to change the code. Does that sound greedy? Cause I am greedy 🤑
tamerj1 Yeah, the problem is that there isn't a SELECTCOLUMNSEXCEPT function that would select all the columns except those that you specify. If you had that function, even still you would have to modify the code if you add an additional column. The only way to achieve a no code revisions would be if you hard coded all of the desired columns into a SELECTCOLUMNS statement or if you could come up with a way to SELECTCOLUMNSIN function where you could pass a table and if the column name appeared in a row value then it would be selected otherwise not. It is possible to create your time table, one is included in the PBIX but I couldn't pull off the last piece of it where I used that as selection criteria for columns.
I came up with a couple additional approaches to lessen the burden on modifying the code. PBIX is attached as well. Also, these approaches seem to make the code more stable across charts types in my testing.
This one abuses the PATH functions even more and uses SUBSTITUTE to find the 2nd comma and replace it. All you need to do then is if you add an additional column to the front then you modify the instance of the comma you are replacing. Note that this version is limited to when you will only ever have a single row of data that you are averaging.
Measure 2 =
VAR __Text = SUBSTITUTE( TOCSV( 'T_MEASURES', , ",", 1 ), UNICHAR(10), "|" )
VAR __1 = PATHITEM( __Text, 1 )
VAR __2 = PATHITEM( __Text, 2 )
VAR __3 = SUBSTITUTE( __2, ",", "|", 2 )
VAR __4 = PATHITEM( __3, 2 )
VAR __Path = SUBSTITUTE( __4, ",", "|" )
VAR __Table =
ADDCOLUMNS(
GENERATESERIES(1, 144, 1),
"__Value", PATHITEM( __Path, [Value]) + 0
)
VAR __Result = AVERAGEX( __Table, [__Value] )
RETURN
__Result
This version attempts to ensure that all "pre" columns are moved to the end prior to grabbing the data. So, if you add a "pre" column, you will need to add an additional DISTINCT and correpsonding SUBSTITUTEWITHINDEX. The index is added to the end of the table so once this is done you can just grab items 1 - 144 every time. I tried multiple ways of trying to eliminate multiple columns at a time using this approach but the function refused to cooperate.
Measure 3 =
VAR __Table = 'T_MEASURES'
VAR __Sensors = DISTINCT('T_MEASURES'[Sensor Tag])
VAR __Dates = DISTINCT('T_MEASURES'[Date])
VAR __Table1 = SUBSTITUTEWITHINDEX( __Table, "Index", __Sensors, [Sensor Tag], ASC)
VAR __Table2 = SUBSTITUTEWITHINDEX( __Table1, "Index1", __Dates, [Date], ASC)
VAR __Text = SUBSTITUTE( TOCSV(__Table2,,,0), ",", "|" )
VAR __Table3 =
ADDCOLUMNS(
GENERATESERIES(1, 144, 1),
"__Value", PATHITEM( __Text, [Value]) + 0
)
VAR __Result = AVERAGEX( __Table3, [__Value] )
RETURN
__Result
tamerj1 Hey, here's a version that doesn't require hardcoding and you can theoretically add as many pre-columns as you want. Basically Measure 2 but with a dynamic finding of the number of commas before the first value column.
Measure 4 = VAR __Text = SUBSTITUTE( TOCSV( 'T_MEASURES', , ",", 1 ), UNICHAR(10), "|" ) VAR __1 = PATHITEM( __Text, 1 ) VAR __FirstColon = FIND(":", __1) VAR __Left = LEFT( __1, __FirstColon ) VAR __Commas = LEN( __Left ) - LEN( SUBSTITUTE( __Left, ",", "" ) ) VAR __2 = PATHITEM( __Text, 2 ) VAR __3 = SUBSTITUTE( __2, ",", "|", __Commas ) VAR __4 = PATHITEM( __3, 2 ) VAR __Path = SUBSTITUTE( __4, ",", "|" ) VAR __Table = ADDCOLUMNS( GENERATESERIES(1, 144, 1), "__Value", PATHITEM( __Path, [Value]) + 0 ) VAR __Result = AVERAGEX( __Table, [__Value] ) RETURN __Resulttamerj1 OK, one "final" version. This one *should* work with multiple rows of sensor data on the same day and doesn't assume basically anything in terms of how many columns of data, just that the first column of data that the name includes a ":" and that after that the data columns are sequential with no non-data columns mixed in.
I don't have the original data file so I can't actually test it with multiple data on the same day (although that doesn't make sense in this scenario it might make sense in other similar circumstances or scenarios so I wanted to try doing it).
Measure 5 = // Deal with the header to figure out what columns need to be removed VAR __TableWHeaders = SUBSTITUTE( TOCSV( 'T_MEASURES', , ",", 1 ), UNICHAR(10), "|" ) // TOCSV with Headers VAR __Headers = PATHITEM( __TableWHeaders, 1 ) //headers VAR __FirstColon = FIND(":", __Headers) // find the first colon, which should be the first column of data VAR __Left = LEFT( __Headers, __FirstColon ) // grab everything to the left of the first colon VAR __Commas = LEN( __Left ) - LEN( SUBSTITUTE( __Left, ",", "" ) ) // figure out how many commas (columns) are in the "pre-columns" before the data // Now deal with the data which might have multiple rows VAR __TableWOHeaders = SUBSTITUTE( TOCSV( 'T_MEASURES', , ",", 0 ), UNICHAR(10), "|" ) // TOCSV without Headers VAR __Count = COUNTROWS('T_MEASURES') // number of rows in table VAR __Data = // Generate a table with a column that strips out the pre-columns ADDCOLUMNS( GENERATESERIES(1, __Count, 1), "__Data", VAR __Text = PATHITEM( __TableWOHeaders, [Value]) VAR __Path = SUBSTITUTE( __Text, ",", "|", __Commas ) VAR __Result = PATHITEM( __Path, 2 ) RETURN __Result ) VAR __DataText = CONCATENATEX( __Data, [__Data], "|" ) // convert the table back into a text string with each row as a path item VAR __Path = SUBSTITUTE( __DataText, ",", "|" ) // convert all columns to path items VAR __DataColumns = LEN( __DataText) - LEN( SUBSTITUTE( __DataText, ",", "" ) ) // figure out how many columns of data we are dealing with VAR __Table = // Convert the path into a table ADDCOLUMNS( GENERATESERIES(1, __DataColumns, 1), "__Value", PATHITEM( __Path, [Value]) + 0 ) VAR __Result = AVERAGEX( __Table, [__Value] ) // run an aggregation against the table RETURN __ResultI don't have enough words to describe my happiness while reading this. I really didn't expect to learn something out of this challenge but indeed you you are one of best teachers I have learned from. The way you approach the problem made me speachless. That opened my eye to many ideas that can realize new methods of solving problems. I realized that I still have so much to learn.
You are right, at some point you have to hardcode something. True that the column order might change and true that more information columns can be added but tye number of value columns and their NAMES remain unchanged. TOCSV ofers more than the values of the columns.
Greg_Deckler, just FYI, your Measure 5 is a bit off. I'd guess some off-by-one bug somewhere.
I'm attaching a pbix that has the data baked in (rather than referencing an Excel file) in case you or others want to tinker with the source data. I used my custom Table.ToM function to produce this (and discovered I hadn't accounted for column names that need to be escaped with a #" " wrapper, so I'll need to update my function to handle that).