cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Using IF , CONTAINS , CONCATENATE in Power Query Editor

Hello I have a table that has 1 column.

Sample:

Column1

Sales

DimProduct

FactCustomer

 

I want to create a custom column with the following rule :

If Column1 contains Dim or Fact then 'DWH.' & 'Column1' else 'ODS.' & Column1

 

Column1 --------- Column2

Sales ------------- ODS.Sales

DimProduct ------ DWH.Product

FactCustomer -----DWH.FactCustomer

Thanks in advance

2 ACCEPTED SOLUTIONS
Super User IV
Super User IV

@adnane_k 
To achieve it in Power Query, add the following code as Custom Column:

 

=if Text.StartsWith(Text.Upper([Column1]),"DIM") or Text.StartsWith(Text.Upper([Column1]),"FACT") then "DWH." & [Column1] else "ODS." & [Column1]




Add this as a new column if you want to use it in the model using DAX:

 

Column2 = 
IF(
    UPPER(LEFT(Table1[Column1],3)) = "DIM" || UPPER(LEFT(Table1[Column1],4)) = "FACT",
    "DWH." & Table1[Column1],
    "ODS." & Table1[Column1]
)

 

Fowmy_0-1610394471549.png

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Super User III
Super User III

@adnane_k in PQ use following expression in add new column

 

(if Text.StartsWith(Text.Proper([Column1]),"Dim") or Text.StartsWith(Text.Proper([Column1]),"Fact") then "DWH." else "ODS.") & [Column1]

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

2 REPLIES 2
Super User III
Super User III

@adnane_k in PQ use following expression in add new column

 

(if Text.StartsWith(Text.Proper([Column1]),"Dim") or Text.StartsWith(Text.Proper([Column1]),"Fact") then "DWH." else "ODS.") & [Column1]

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Super User IV
Super User IV

@adnane_k 
To achieve it in Power Query, add the following code as Custom Column:

 

=if Text.StartsWith(Text.Upper([Column1]),"DIM") or Text.StartsWith(Text.Upper([Column1]),"FACT") then "DWH." & [Column1] else "ODS." & [Column1]




Add this as a new column if you want to use it in the model using DAX:

 

Column2 = 
IF(
    UPPER(LEFT(Table1[Column1],3)) = "DIM" || UPPER(LEFT(Table1[Column1],4)) = "FACT",
    "DWH." & Table1[Column1],
    "ODS." & Table1[Column1]
)

 

Fowmy_0-1610394471549.png

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors