How to transpose Day of Month columns to rows in a table?

[ironryan77]

I created a table that looks like this, where there are columns for the Month, several account details, and one column for each Day of the Month.  The reason I created a separate column for every day of the month as opposed to one Day of the Month column containing all of the Day values is that I wanted there to be one row per transaction.  (I have another version of this dataset with one Day of the Month column, but I could not get this to display as separate rows in my Power BI table without duplicating transactions).

Also, the Month and Day columns are not DateTime datatypes, so I can't use Power BI's calendar or time functions. 

Now I want to transpose this entire table so that all columns become rows.  The end result is there should be one column per transaction.  There are 330,000 transactions (rows) in my dataset.  I tried using Power BI's Transpose Transform within the Query Editor, but after waiting 30 minutes with no results, I gave up on that effort.  Every time I have used the Query Editor it seems extremely slow.  The solution I most like the sound of is using DAX to create a new table based on my table.   Case in point was this thread the solution posted by Phil:

https://community.powerbi.com/t5/Desktop/Rows-not-columns/m-p/135253/highlight/true#M57924

This sample table below is a simplified version.  There are many more account details not included here. 

create table #MonthDayExample
(
	DOS_YrMonth int
	,account_detail1 varchar(5)
	,account_detail2 varchar(5)
	,[01] decimal(13,2)
	,[02] decimal(13,2)
	,[03] decimal(13,2)
	,[04] decimal(13,2)
	,[05] decimal(13,2)
	,[06] decimal(13,2)
	,[07] decimal(13,2)
	,[08] decimal(13,2)
	,[09] decimal(13,2)
	,[10] decimal(13,2)
	,[11] decimal(13,2)
	,[12] decimal(13,2)
	,[13] decimal(13,2)
	,[14] decimal(13,2)
	,[15] decimal(13,2)
	,[16] decimal(13,2)
	,[17] decimal(13,2)
	,[18] decimal(13,2)
	,[19] decimal(13,2)
	,[20] decimal(13,2)
	,[21] decimal(13,2)
	,[22] decimal(13,2)
	,[23] decimal(13,2)
	,[24] decimal(13,2)
	,[25] decimal(13,2)
	,[26] decimal(13,2)
	,[27] decimal(13,2)
	,[28] decimal(13,2)
	,[29] decimal(13,2)
)
insert #MonthDayExample values(201705,'A','B',100,0,0,300,500,0,0,0,0,0,0,20,30,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
insert #MonthDayExample values(201705,'A','C',0,0,0,280,500,0,0,0,0,0,0,75,30,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
insert #MonthDayExample values(201705,'A','D',70,0,0,66,65,0,0,0,0,0,0,20,30,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
insert #MonthDayExample values(201705,'F','G',90,0,0,25,500,0,0,0,0,0,0,20,56,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
insert #MonthDayExample values(201705,'F','H',13,0,0,25,500,0,0,0,0,0,0,20,30,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)

select * from #MonthDayExample

[v-jiascu-msft · Accepted solution]

Hi @ironryan77,

Maybe you used a wrong function. "Transpose" makes all columns become rows, but also makes all rows become columns at the same time. That means there are 330000 columns, which cause the Query Editor stuck. 

We could use "Unpivot all selected columns". I tested it with 3 millions rows and 25 columns. It's a little slow only when applied. Please have a try.

Best Regards!

Dale