Forum Discussion

Venson's avatar
Venson
Frequent Visitor
2 years ago

Mapping data based on the column header and content

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 ...
Need Help
Daniel29195's avatar
Daniel29195
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
Venson
Frequent Visitor
2 years ago

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],
    " ; "
)