Forum Discussion

Npg3's avatar
Npg3
New Member
3 years ago
Solved

Power BI time functions

Hi,    I want to create a function in power bi to creates a new colum and adds a certain amount of time depending on a key word that is a period of time in a different column.  example: row 1...
Datefunctions
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Npg3 ,

     

    You can try M Code as below.

    Date.AddMonths(
    [Column A],
    if [Column B] = "AOT" then 6 
    else if [Column B] = "XYZ" then 3 
    else if [Column B] = "ABC" then 1 
    else null)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Npg3's avatar
Npg3
New Member
3 years ago

It shows expression.error: the name 'EDATE' wasn't recognized. Make sure it's spelled right

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Npg3 

The code is for DAX calculated column not Power Query M language. 

  • Npg3's avatar
    Npg3
    New Member
    3 years ago

    How can I create this function in a custom column in power BI?

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Npg3 

      You may insert conditional column. Should be straightforward. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Npg3 ,

       

      You can try M Code as below.

      Date.AddMonths(
    [Column A],
    if [Column B] = "AOT" then 6 
    else if [Column B] = "XYZ" then 3 
    else if [Column B] = "ABC" then 1 
    else null)

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Npg3's avatar
        Npg3
        New Member
        3 years ago

        This works but the problem is if I want column B to have AOT and ABC in one row it does not work. The way the data is pulled in is in some rows of columb B they have multiple (codes). How do I have power bi choose the code with the lowest timeframe. I tried using LOOKUPVALUE but it will not recognize LOOKUPVALUE people have been using power querey for that function which I do not have. Any advice?