Mapping data based on the column header and content

[Venson]

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 🙂

1. In the database, we have all vendor information, specification and key attribute as below

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

2. next step, our manger define a checklist questions base on different kind of attribute as below.

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

3. next step, we collect "vendor a" and "vendor b" response which product they checked, two tables as below

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

Checklist	Attribute requirement	Product
Question1	Attribute1(yes) and Attribute2(yes)	b1
Question2	Attribute1(yes) and Attribute3(A)	b1
Question3	All	b1 ; b2
Question4	Attribute1(yes) or Attribute3(B)	b3

Final mapping result I'd like to show on dashabord.

Checklist	Attribute requirement	Product	Mapping success	Mapping miss	Mapping wrong	Mapping success count	Mapping miss count	Mapping wrong count
Question1	Attribute1(yes) and Attribute2(yes)	a1 ; a2 ; b1	b1	a3	a1 ; a2	1	1	2
Question2	Attribute1(yes) and Attribute3(A)	a1 ; b1	a1	a2 ; a3	b1	1	2	1
Question3	All	a1 ; b1 ; b2	a1 ; b1 ; b2	a2 ; a3	N/A	3	2	0
Question4	Attribute1(yes) or Attribute4(C)	a1 ; b3	a1	a2 ; a3 ; b1	b3	1	3	1

[Daniel29195]

@Venson hello, 

kindly find attached thee file . 

https://drive.google.com/file/d/1PBjdX9cFc2hCL_gMg6dCzGUdZ8Jk7NFA/view?usp=sharing

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 .

hope this makes sense to you .

best regards. 

[Venson]

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 ?

[Daniel29195]

@Venson 

i will help you out dont worry 

to expand the a1 ; b1 to multiple rows,  follow the following steps : 

step1 :  go to poewr query

step2 :  select the table in question

step3 :  select the column in question 

step 4 ; go to transform 

step 5 :  under transform choose --_>  split colums --> by delimiter 

step 6 : configure as below : 

done 

this will expand the row into multiple rows. 

if this helps you, dont forget to hit that thumbs up 👍 button . 

let me know if you have any further questions  . 

[Venson]

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"

1. correct answer column is to show the correct answer after compare table 2 & 4

2. need vendor to check column is to point out which product vendor miss out to comfirm

I tried to modify the measure but still can not get it, may please help me ?

Measure_Mapping Correct Answer =

VAR datasource1 =

ADDCOLUMNS(

    SUMMARIZE(

        'Table (2)',

        'Table (2)'[Product],

        'Table (2)'[attribute1],

        'Table (2)'[Attribute2],

        'Table (2)'[Attribute3],

        'Table (2)'[Attribute4]

       ),

    "check",

    SWITCH(

        TRUE(),

        SELECTEDVALUE('Table (4)'[Checklist]) = "Quesiton1" && ('Table (2)'[attribute1] = "Yes" && 'Table (2)'[Attribute2] = "Yes") , 1,

        SELECTEDVALUE('Table (4)'[Checklist]) = "Quesiton2" && ('Table (2)'[attribute1] = "Yes" && 'Table (2)'[Attribute3] = "A") , 1,

        SELECTEDVALUE('Table (4)'[Checklist]) = "Question3", 1,

        SELECTEDVALUE('Table (4)'[Checklist]) = "Quesiton4" && ('Table (2)'[attribute1] = "Yes" || 'Table (2)'[Attribute3] = "B") , 1,

        0

    )

)

VAR filter_datesource1 =

FILTER(

    datasource1,

    [check] = 1

)

return  

CONCATENATEX(

    filter_datesource1,

    'Table (2)'[Product],

    " ; "

)