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

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
Fowmy
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

parry2k
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
parry2k
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

Fowmy
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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.