Forum Discussion

motionid's avatar
motionid
New Member
4 years ago
Solved

Create column from another column if row does not contain...

Hi all,

Very new to DAX and the Power BI world.

 

Was wondering if I would write the following where rows are extracted and placed into a new column if the column does NOT equal xxx content.

i.e. I would like to move all rows except what is listed.

 

 

 

 

= Table.AddColumn(#"Removed Columns", "Non Billable Hours", each if 
  Text.Contains([Project Name] ,"Holiday") or Text.Contains([Project Name] ,"Personal Day") or Text.Contains([Project Name] ,"Japan - Internal Work") or Text.Contains([Project Name] ,"Japan - Training/Education/Committee Activities") or Text.Contains([Project Name] ,"null")
then 
  try 
    Value.FromText([Project Name]) 
  otherwise 
    null 
else 
    null)

 

 

 

Any suggestions greatly appreciated.
Cheers!

  • Hi motionid 

     

    You can use Conditional Column in Power Query (pls refer to this video: https://youtu.be/7UgqPhu1rN0)

    in Power Query Editor ribbon --> Add columns --> Conditional Columns

    example:

     #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Custom", each if Text.Contains([Fee Type], "Revenue") then [Fee Type] else if Text.Contains([Fee Type], "RRR") then [Fee Type] else null)

    you will also get a nice dialog box to customize the inputs

     

     

    Thanks & Regards,
    Mohammed Adnan

    Learn Power BI: https://www.youtube.com/c/taik18

3 Replies

  • mohammedadnant's avatar
    mohammedadnant
    Impactful Individual

    Hi motionid 

     

    You can use Conditional Column in Power Query (pls refer to this video: https://youtu.be/7UgqPhu1rN0)

    in Power Query Editor ribbon --> Add columns --> Conditional Columns

    example:

     #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Custom", each if Text.Contains([Fee Type], "Revenue") then [Fee Type] else if Text.Contains([Fee Type], "RRR") then [Fee Type] else null)

    you will also get a nice dialog box to customize the inputs

     

     

    Thanks & Regards,
    Mohammed Adnan

    Learn Power BI: https://www.youtube.com/c/taik18

  • Hi mohammedadnant ,

    Really appreciate you responding and providing me with the solution.

    This was exactly what I needed.

     

    Big thank you.

     

    Cheers,

    Sean.

     

    • mohammedadnant's avatar
      mohammedadnant
      Impactful Individual

      Hi motionid 

       

      Thank you for marking my answer as a solution, could you also pls give thumbs up (kudos) to the solution.

       

      Thanks & Regards,
      Mohammed Adnan