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
Community Champion
2 years ago

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

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's avatar
    Daniel29195
    Community 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],
          " ; "
      )