topic Re: Xlookup from string in Report Server

Xlookup from string

danishwahab — Mon, 30 Sep 2024 06:57:22 GMT

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!

Re: Xlookup from string

uzuntasgokberk — Mon, 14 Jul 2025 19:18:22 GMT

Hello danishwahab,

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

Re: Xlookup from string

danishwahab — Mon, 30 Sep 2024 07:10:09 GMT

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.

Re: Xlookup from string

uzuntasgokberk — Mon, 14 Jul 2025 19:18:02 GMT

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.

Re: Xlookup from string

Anonymous — Tue, 01 Oct 2024 04:06:10 GMT

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.

Re: Xlookup from string

danishwahab — Tue, 01 Oct 2024 04:58:55 GMT

Thank you Anonymous ,

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

Thanks!