Forum Discussion
Looking to Replicate Excel Formulas into PBI
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
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)