Mapping data based on the column header and content

Question

Hi everyone,Thanks for your times here.I got one request is to review vendor's response and auto mapping the result.Tried some method but fail,Below is my raw data. hope can have your kindly support here. many thanks 🙂&nbsp;1. In the database, we have all vendor information, specification and key attribute as belowSpecAttribute1Attribute2Attribute3Attribute4vendorproductSpecAYesNoACaa1SpecAYesNoACaa2SpecBYesYesACaa3SpecCYesYesBDbb1SpecDNoYesBDbb2&nbsp;2. next step, our manger define a checklist questions base on different kind of attribute as below.ChecklistAttribute requirementQuestion1Attribute1(yes) and Attribute2(yes)Question2Attribute1(yes) and Attribute3(A)Question3AllQuestion4Attribute1(yes) or Attribute3(B)&nbsp;3. next step, we collect "vendor a" and "vendor b" response which product they checked, two tables as belowChecklistAttribute requirementProductQuestion1Attribute1(yes) and Attribute2(yes)a1 ; a2Question2Attribute1(yes) and Attribute3(A)a1Question3Alla1Question4Attribute1(yes) or Attribute3(B)a1&nbsp;ChecklistAttribute requirementProductQuestion1Attribute1(yes) and Attribute2(yes)b1Question2Attribute1(yes) and Attribute3(A)b1Question3Allb1 ; b2Question4Attribute1(yes) or Attribute3(B)b3&nbsp;Final mapping result I'd like to show on dashabord.ChecklistAttribute requirementProductMapping successMapping missMapping wrongMapping success countMapping miss countMapping wrong countQuestion1Attribute1(yes) and Attribute2(yes)a1 ; a2 ; b1b1a3a1 ; a2112Question2Attribute1(yes) and Attribute3(A)a1 ; b1a1a2 ; a3b1121Question3Alla1 ; b1 ; b2a1 ; b1 ; b2a2 ; a3N/A320Question4Attribute1(yes) or Attribute4(C)a1 ; b3a1a2 ; a3 ; b1b3131

daniel29195 · Answer

Venson&nbsp;hello,&nbsp;&nbsp;kindly find attached thee file .&nbsp;https://drive.google.com/file/d/1PBjdX9cFc2hCL_gMg6dCzGUdZ8Jk7NFA/view?usp=sharing&nbsp;if this is what you want, you need some transformations on your data to be able to get the output you want .i didnt create all the required measures, cz the measures will be same as the second one, just you need to change the conditions in the switch statement in the code .&nbsp;hope this makes sense to you .&nbsp;&nbsp;&nbsp;&nbsp;best regards.&nbsp;

daniel29195 · Answer

Venson&nbsp;i will help you out dont worry&nbsp;to expand the a1 ; b1 to multiple rows,&nbsp; follow the following steps :&nbsp;&nbsp;step1 :&nbsp; go to poewr querystep2 :&nbsp; select the table in questionstep3 :&nbsp; select the column in question&nbsp;step 4 ; go to transform&nbsp;step 5 :&nbsp; under transform choose --_&gt;&nbsp; split colums --&gt; by delimiter&nbsp;&nbsp;step 6 : configure as below :&nbsp;&nbsp;&nbsp;done&nbsp;this will expand the row into multiple rows.&nbsp;&nbsp;&nbsp;&nbsp;if this helps you, dont forget to hit that thumbs up 👍 button .&nbsp;&nbsp;let me know if you have any further questions&nbsp; .&nbsp;

venson · Answer

Hi Sir, many thanks 🙂 !!as you mentioned, might need some transform from row data to achieve the table 4 as yours, I can combine two vendor's reponse trough feature "apped", but don't know how to make it become seperated row, for example, vendor a put product a1 ; a2 for Quesiton1, and I tried some pivot or tranpose still can't make it become two row, may please help me ?

venson · Answer

Hi Sir, really appreciate your support !!I got one problem that I wanna got measure column with the "correct answer column" and "need vendor to check column"&nbsp;1. correct answer column is to show the correct answer after compare table 2 &amp; 42. need vendor to check column is to point out which product vendor miss out to comfirm&nbsp;I tried to modify the measure but still can not get it, may please help me ?Measure_Mapping Correct Answer =VAR datasource1 = ADDCOLUMNS(&nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; 'Table (2)',&nbsp; &nbsp; &nbsp; &nbsp; 'Table (2)'[Product],&nbsp; &nbsp; &nbsp; &nbsp; 'Table (2)'[attribute1],&nbsp; &nbsp; &nbsp; &nbsp; 'Table (2)'[Attribute2],&nbsp; &nbsp; &nbsp; &nbsp; 'Table (2)'[Attribute3],&nbsp; &nbsp; &nbsp; &nbsp; 'Table (2)'[Attribute4]&nbsp; &nbsp; &nbsp; &nbsp;),&nbsp; &nbsp; "check",&nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Table (4)'[Checklist]) = "Quesiton1" &amp;&amp; ('Table (2)'[attribute1] = "Yes" &amp;&amp; 'Table (2)'[Attribute2] = "Yes") , 1,&nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Table (4)'[Checklist]) = "Quesiton2" &amp;&amp; ('Table (2)'[attribute1] = "Yes" &amp;&amp; 'Table (2)'[Attribute3] = "A") , 1,&nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Table (4)'[Checklist]) = "Question3", 1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE('Table (4)'[Checklist]) = "Quesiton4" &amp;&amp; ('Table (2)'[attribute1] = "Yes" || 'Table (2)'[Attribute3] = "B") , 1,&nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; ))VAR filter_datesource1 = FILTER(&nbsp; &nbsp; datasource1,&nbsp; &nbsp; [check] = 1)return &nbsp;CONCATENATEX(&nbsp; &nbsp; filter_datesource1,&nbsp; &nbsp; 'Table (2)'[Product],&nbsp; &nbsp; " ; ")&nbsp;&nbsp;

Forum Discussion

Mapping data based on the column header and content

4 Replies

Related content

Copy Job - Column Mapping

The content is blocked.

Notebook Error com.fasterxml.jackson.databind.exc.MismatchedInputException: No content to map due to

Name-based column mappings

Add New Column with Dynamic content in Copy Activity

Spec	Attribute1	Attribute2	Attribute3	Attribute4	vendor	product
SpecA	Yes	No	A	C	a	a1
SpecA	Yes	No	A	C	a	a2
SpecB	Yes	Yes	A	C	a	a3
SpecC	Yes	Yes	B	D	b	b1
SpecD	No	Yes	B	D	b	b2

Checklist	Attribute requirement
Question1	Attribute1(yes) and Attribute2(yes)
Question2	Attribute1(yes) and Attribute3(A)
Question3	All
Question4	Attribute1(yes) or Attribute3(B)