Display wide tables vertically instead of horizontally using key-value pair tables - Hands On Demo!
If you are not interested in using SQL server and learning cool stuff about Key-Val Pair tables, and want an easier way to produce the same Power BI report, then skip this video and go to my Power BI(15) video, here: • Display your table columns vertically inst... By creating a view that makes your fat tables skinny and tall, you can get a display more like a form in your reports. In many situations this may be more beneficial than the typical horizontal table approach. Watch this video and I will explain how, with a little sql code, you can achieve this by emulating key-value pair tables. In fact the SQL template I use in the video is shown below:
( / greg-c-coopman-b07b54103 )
DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @PrimaryKey NVARCHAR(MAX) = 'EmployeeKey'
DECLARE @TableNameWithSchema NVARCHAR(MAX) = 'dbo.dimEmployee'
SELECT @sql += CHAR(13) + CHAR(10)
'SELECT ' + @PrimaryKey + ',
[Key] = ''' + REPLACE(name, '''', '''''') + ''',
Value = CAST(' + QUOTENAME(name) + ' AS NVARCHAR(MAX)) '
+' FROM ' + @TableNameWithSchema + '
UNION ALL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@TableNameWithSchema);
SET @sql = LEFT(@sql, LEN(@sql)-9) + ';';
PRINT @sql;
EXEC(@sql)