Solved: How to create customs columns from several fileds ...

ThamerTK · ‎10-14-2023

Hi, I'm new to Power BI. I would like to crate several custom columns that are formed using logical conditions. In Excel, I can do that for example as follows: =IF(OR(AK7="NA",AL7="NA"),"NA",IF(OR(TODAY()>AK7,TODAY()-AL7>5*365),0,1)), were AK7, AL7, and Today() are columns and function. I have many like that. But I'm facing difficulties in doing this in Power BI.

Can you help in providing a genral syntax to do that. for example, referring to AK7m and AL7 as column 1 and Column 2, and how to use function like Today() with its correct syntax in Power BI?

Ritaf1983 · ‎10-14-2023

Hi @ThamerTK
Today() works in the same way as in eExcel.
About others

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

Anonymous · ‎10-17-2023

Hi @ThamerTK ,

Custom columns are created in Power Query by M language.

I'm sorry for my reconfirmation. Do you want to create a measure, a calculated column, or a custom in Power BI? Measure and calculated columns are created by DAX and will only appear in Power BI Desktop.

For now, if you want custom columns in Power Query, please try:

   if [Column1] = "NA" or [Column2] = "NA" then "NA"
   else if DateTime.LocalNow() > [Column1] or DateTime.LocalNow() - [Column2] > #duration(5, 0, 0, 0) then 0
   else 1

Best Regards,

Stephen Tao

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