Looking to Replicate Excel Formulas into PBI

[jmarino8]

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

[jmarino8]

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

[v-zhangtin-msft]

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 · Accepted solution]

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 

[jmarino8]

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)

[jmarino8]

Thank you for your response, I am getting errors unfortunately when I try to create a new column and plug in that DAX code.