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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Vlookup /Matching Rows at Query

Hi Guys,

Wondering if you can assist me with the below.

Working with some data that is submitted by customers through a webpage. There are a few different webpages that contain two forms which are independent, but when the user hits submit, both the forms submit at the same time.

Webpage 1: Retail Inquiry Form + Product Interest Form

Webpage 2: Wholesale Inquiry + Product Interest Form

Webpage 3: Trade Account+ Product Interest Form

Each form output a single row with multiple column. Because of the way the CMS works, the Appeal Source and certain other columns are only stored for the first form. I would like to populate these missing columns from the first form. In this instance, I would like to populate the Appeal source for the Form 'Product Interest'. 

All the data is exported to a single table in the database. I plan to import this data twice, once matching anything in the column 'FORM ID' = Product Interest and another time != Product Interest.

Then I aim to match several columns together and then if there is a match, output the Appeal source that exists, however I am struggling to get going. 

As the form submits both forms at the same time, the date and time will match for both form submission. The customer ID will be unique and is generated based on their email. Because there could be a chance that individuals submit the form at the same time, I would like to match, 'date' + 'time' + 'customer id'. If this match, then output appeal id in a new column. 

The Appeal Source is a conditional column at Query Level.

3 REPLIES 3
Anonymous
Not applicable

What the data looks like.

 

Customer IDEmailForm IDDateTimeAppeal
0001mark@example.comRetail Inquiry24 June 201913:16:36Classifieds
0001mark@example.comProduct Interest24 June 201913:16:36 
0002john@example.comTrade Account24 June 201913:24:07email
0002john@example.comProduct Interest24 June 201913:24:07 
0003david@example.comWholesale Inquiry24 June 201913:33:28google
0003david@example.comProduct Interest24 June 201913:33:28 
0004pablo@example.comRetail Inquiry24 June 201919:08:49Trade Show - Domotex
0004pablo@example.comProduct Interest24 June 2019

19:08:49

 
0005ryan@example.comTrade Account24 June 2019

19:08:49

 
0005ryan@example.comProduct Interest24 June 2019

19:08:49

 

Hi @Anonymous ,

 

What is your desired output with above samle table?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable


@v-yulgu-msft wrote:

Hi @Anonymous ,

 

What is your desired output with above samle table?

 

Regards,

Yuliana Gu


Hi,

 

Once I split the data into tables:

 

One Table will contain everything that matches Form ID = Product Interest

 

Another Table will contain everything else that does not match Form ID = Product Interest

 

Ideally I would like each row to have the Appeal label, by matching the columns: Customer ID; Time; Date

 

Thanks

Figo

 

Table 1:

Customer IDEmailForm IDDateTimeAppeal
0001mark@example.comProduct Interest24 June 201913:16:36Classified
0002john@example.comProduct Interest24 June 201913:24:07email
0003david@example.comProduct Interest24 June 201913:33:28google
0004pablo@example.comProduct Interest24 June 2019

19:08:49

Trade Show - Domotex
0005ryan@example.comProduct Interest24 June 2019

19:08:49

 

 

Table 2:

 

Customer IDEmailForm IDDateTimeAppeal
0001mark@example.comRetail Inquiry24 June 201913:16:36Classifieds
0002john@example.comTrade Account24 June 201913:24:07email
0003david@example.comWholesale Inquiry24 June 201913:33:28google
0004pablo@example.comRetail Inquiry24 June 201919:08:49Trade Show - Domotex
0005ryan@example.comTrade Account24 June 2019

19:08:49

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.