Columns to Rows.

[Anonymous]

I have the following data:

 name    --   january   --   february --  and so on...

  Foo      --    10          --     72.2      --         ....

  Bar       --     89.1      --     108.4    --         ...

But, I want to change it to this:

  name  --   month    --    value

    Foo   --   january   --     10     

    Foo   --  february  --     72.2 

          ... all next months

    Bar   --   january    --     89.1

    Bar   --   february  --    108.4

I want to learn how can I do that using DAX and M? One example using only M and the other using only Dax. 

[Anonymous · Accepted solution]

Hi caarlos

in Power Query (M) use unpivot other columns.

I never use DAX to solve this as I prefer M

[Phil_Seamark · Accepted solution]

Hi @Anonymous

Here is a DAX based solution.  I reckon PQ is the better way to go but this is what it might look like in DAX.  I've assumed your table is called Table and the columns are as per your sample

Table = 
Var TableMonths = DataTable(
                "MonthName", STRING,{  
                        {"January"},{"February"},{"March"},{"April"},{"May"},{"June"},{"July"},{"August"},{"September"},{"October"},{"November"},{"December"}    
                        }  
                    ) 
Var CrossJoined = CROSSJOIN(Table1,TableMonths)                    
Return    SELECTCOLUMNS( CrossJoined , 
            "Name" , [Name] , 
            "Month" , [MonthName] , 
            "Value" , SWITCH([MonthName] , 
                "January" , [January] ,
                "February" , [February] ,
                "March" , [March] ,
                "April" , [April] ,   
                "May" , [May] ,
                "June" , [June] ,
                "July" , [July] ,
                "August" , [August] ,
                "September" , [September] ,
                "October" , [October] ,
                "November" , [November] ,
                "December" , [December] ,
                BLANK())                )

[liuruby8]

Thank you so much for this. It is really helpful and I used this to solve a work problem!!

[Anonymous]

Thank you, @Phil_Seamark and @Anonymous, both examples worked!

[Anonymous]

Thank you, @Phil_Seamark and @Anonymous, both examples worked!

[Anonymous · Accepted solution]

Hi caarlos

in Power Query (M) use unpivot other columns.

I never use DAX to solve this as I prefer M

[flopezy2k]

Thanks to share it. I really appreciated your help.