Forum Discussion
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
Hello Anonymous ,
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
5 Replies
- dharmendars007
Memorable Member
Hello Anonymous ,
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
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)