If Text Contains Statement

[Dane]

I have 2 related tables (one showing tracking history at different work cells and the other showing order details). I am trying to create a column in my Tracking History table that will calculate Linear Inches using the following logic:

If OrderDetails[PartNumber] contains "2L" then TrackingHistory[Width]*2 else (TrackingHistory[Width]*2) + (TrackingHistory[Height]*2)

Any recommendations?

[Dane]

Sorry it took me a couple of days to follow up. I was in a conference late last week. Attached is the link to the sharepoint site that has this file.

https://dreamwalls.sharepoint.com/sites/PowerBI/Shared%20Documents/Forms/AllItems.aspx

[Ashish_Mathur]

Hi,

That takes me to a sign-in page.  Please upload the file to Google Drive/One Drive and share the download link of that file.

[Dane]

Below is the download link in google drive, please let me know if you have trouble accessing this. Thanks!

https://drive.google.com/file/d/1cIU1YZwPzxCC7vM1w1gDMYrGVVngQIIv/view?usp=sharing

[Ashish_Mathur]

Since you have not shared the sourcee Excel file, I cannot see your table in the queru Editor and there cannot provide a M language solution.  However, this calculated column formula works

Column = if(CONTAINSSTRING(OrderDetail[PartNo],"2L"),OrderDetail[Width]*2,(OrderDetail[Width]*2)+(OrderDetail[Height]*2))

[Ashish_Mathur]

Hi,

Try this formula in the Query Editor

=if Text.Contains(OrderDetails[PartNumber], "2L", Comparer.OrdinalIgnoreCase) then TrackingHistory[Width]*2 else (TrackingHistory[Width]*2) + (TrackingHistory[Height]*2)

Hope this helps.

[Dane]

I tried the formula you suggested and am getting an error in the column, the bottom of the page says "Expression.Error: We cannot convert a value of type list to type text.

Details:

Value=[List]

Type=[Type]

[Ashish_Mathur]

Share the download link of your PBI file.

[tackytechtom · Accepted solution]

Hi @Dane ,

Does this one here work for you?

IF (
    SEARCH ( 
        "2L",
        OrderDetails[PartNumber],
        1,
        BLANK()
    ),
    TrackingHistory[Width] *2,
    ( TrackingHistory[Width] *2 ) + ( TrackingHistory[Height] *2 )
)

I pressumed that your logical test shall search for "2L" in PartNumber, meaning "2L" could come up at different positions in a string (i.e. "ABC 2L", "2L DEF", "GHI 2L JKL" etc.). 

Let me know if this helps and if not, feel free to share some sample data and I can take a deeper look into it 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Dane]

I tried your solution but could not bring in OrderDetails[PartNo] into the formula for some inexplicable reason. Can I email you the PBIX I have?

[tackytechtom]

Hi Dane,

I'd suggest that you upload your pbix file to a sharepoint or public dropbox folder and paste the link into the forum. With that others will be able to help you, too!

Alternatively, you could just paste a few rows of your two tables OrderDetails and TrackingHistory. Make sure to explain their connection as well 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/