Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jmarino8
Regular Visitor

Looking to Replicate Excel Formulas into PBI

Hi,
First and foremost I am relatively new to using PowerBI, however I am currently trying to replicate formulas in the following .xlsx file under Columns E-K into PowerBI. What would the best way be to accomplish this? Thanks
-Joe

 

1 ACCEPTED SOLUTION
dharmendars007
Super User
Super User

Hello @jmarino8 , 

 

There are N/A and 0 in your spreadsheet how ever when am trying to see the formula in Column E, I assume it is a Conditinal colum , Therefore either you can use If condition or Switch Statement in Power BI as calculated column to create this, here is a example code you can try..

 

Product Name =
SWITCH(TRUE(),
CONTAINSSTRING([C], "Half-Gallons"), "Half-Gallons",
CONTAINSSTRING([C], "Pints"), "Pints",
CONTAINSSTRING([C], "Minis"), "Minis",
CONTAINSSTRING([C], "Three-Gallon"), "Three-Gallons",
CONTAINSSTRING([C], "3 Gallon"), "Three-Gallons",
BLANK())

 

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

 

Thank You

Dharmendar S

LinkedIN 

View solution in original post

5 REPLIES 5
jmarino8
Regular Visitor

Ended up figuring it out, Appreciate all the help. Thanks

v-zhangtin-msft
Community Support
Community Support

Hi, @jmarino8 

 

Have you solved your problem? If not yet solved, can you tell us what you expect the output to be?

 

Best Regards,

Community Support Team _Charlotte

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

dharmendars007
Super User
Super User

Hello @jmarino8 , 

 

There are N/A and 0 in your spreadsheet how ever when am trying to see the formula in Column E, I assume it is a Conditinal colum , Therefore either you can use If condition or Switch Statement in Power BI as calculated column to create this, here is a example code you can try..

 

Product Name =
SWITCH(TRUE(),
CONTAINSSTRING([C], "Half-Gallons"), "Half-Gallons",
CONTAINSSTRING([C], "Pints"), "Pints",
CONTAINSSTRING([C], "Minis"), "Minis",
CONTAINSSTRING([C], "Three-Gallon"), "Three-Gallons",
CONTAINSSTRING([C], "3 Gallon"), "Three-Gallons",
BLANK())

 

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

 

Thank You

Dharmendar S

LinkedIN 

Essentially I have 5 columns in Excel I want to figure out a way to translate the formuals I have into PowerBI:
Column1:

=INDEX({"Half-Gallons","Pints","Minis","Three-Gallons","Three-Gallons",""},MATCH(1,COUNTIF(C2,"*"&{"Half-Gallons","Pints","Minis","Three-Gallon","3 Gallon",""}&"*"),0))

 

Column2:

=IF(C2<>"",E2,"")

 

Column3:

=IF(G2="",F2,CONCAT(E2," -- ",F2))

 

Column4:

=IFERROR(INDEX($H$2:$H$5000,MATCH(0,COUNTIF($J$1:J1,$H$2:$H$5000),0)),"")

 

Column5:

=SUMIF(H:H,J2,I:I)

 

Thank you for your response, I am getting errors unfortunately when I try to create a new column and plug in that DAX code.
2024-09-19 08_23_36-Untitled - Power BI Desktop.png

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.