Xlookup from string

[danishwahab]

Dear All,

We are getting data in the below format where we are getting Raw value against each point and we need to identify it's state based on the metata in the State column coming in as a string value.

E.g. Point ID 1 should have occupancy state as Unoccupied where as Point ID 2 should have occupancy state as Occupied.

Expected Values

We are dealing with huge timeseries sensors data coming in every 15 minutes. Kindly provide us the logic DAX or Custom column logic which should have minimum performance impact. Also please suggest is this too bad to implement this in Power BI?

Thanks!

[v-jianpeng-msft · Accepted solution]

Thank you uzuntasgokberk 

Hi, @danishwahab 

You can do this in Power Query in Power BI. Here are the detailed steps:

First I have the following dataset:

To open the Advanced Editor:

Copy the following M code into the Advanced Editor:

ExtractText = Table.AddColumn(#"Changed Type", "Extracted Text", each Text.BetweenDelimiters(_[State],Text.From( _[Raw Data Value]) & "=",";"))

The results are as follows:

I have uploaded the PBIX file I used for this post below.

Best Regards

Jianpeng Li

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

[danishwahab]

Thank you @v-jianpeng-msft ,

This worked perfectly as expected. Thanks for all your efforts and help in this. 

Thanks!

[uzuntasgokberk]

Hello @danishwahab,

That would be better make a relationship Instead of writing a dax code f the perfomance better for you.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

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

[danishwahab]

Thanks @uzuntasgokberk  for your response.
For this how can we convert this to a table as the metadata is in string format with multiple values in a single column. As per my understanding we have to create a separate table status value in each row against each Point ID.

[uzuntasgokberk]

Hello @danishwahab,

You can use Power Query(Transform Data), you can duplicate the fact table or referance and select columns that neccesary and remove duplicates. Make sure your created table has unique rows. Close & Apply and make relationship 1 to Many.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

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