Forum Discussion

caoellipsis's avatar
caoellipsis
New Member
8 years ago
Solved

Shaping Month/Day/Year Problem

Hi there, This is my first post.  I can't figure out how to get the date right.   This is data for price per barrel of oil by month and year.       I want the month and year data to be shaped in...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You first change the number format of the first column to Whole Number and then "Unpivot other columns".  Rename the attribute column as Months.  Click on Close and Load.  Then write this calculated column formula

     

    =1*("1/"&[Month]&"/"&[Year])

     

    Format this calculated column as Date.

     

    Hope this helps.

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
8 years ago

caoellipsis,

 

Is this what you're going for?:

 

 

Unpivot the columns [Jan], [Feb], [Mar], etc. I used the built-in UI for this.

 

 

Use the following article - https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/

I modified to the following:

 

(input) =>
let
values = {
    {"Jan", 1},
    {"Feb", 2},
    {"Mar", 3},
    {input, "Undefined"}

},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result

After you have the custom function, you can use it as an Add Column:

 

fnSwitchMMM([Attribute])

Lastly, you can now use DateTime.From ( ) as an Add Column then change Data Type to Date:

DateTime.From(#date([Column1],[Custom],1))

*review the 3rd argument for #date