Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
devika
Helper II
Helper II

Extract and search list and record

Hi all,

 

I am trying to extract  and search the List and Records from JSON column.

 

Here is my query steps so far and below is the explaination of one row

devika_6-1648687323028.png

 

1. Here is the record for each row

devika_1-1648686481485.png

 

2. In the each record there are few values.

 

devika_2-1648686574212.png

 

3. I need to search only in the Notes. It is a 'List' .

 

devika_3-1648686723280.png

 

4. In each list there are multiple records it can goup to 50.

In this example, there are 18 case notes left.

 

5. Here is the structure for each record. In the each record, I have to look for "Conversation with" row and need to search for a string "RTB", if 'RTB' existing then the original row it should display 1. In this example, 63207 should have 1

devika_4-1648686895810.png

 

Can I get some guidance to proceed further pls?

 

 

 

 

1 REPLY 1
johnt75
Super User
Super User

I think you can add a new custom column along the lines of

if List.MatchesAny( [CaseJson][Notes], each _[ConversationWith] = "RTB") then 1 else 0 end

The syntax may not be quite right but I think the general principle will work

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.