Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Changing Data Format

Hi guys,

 

I have a table like the one on the left. I want to transform this table into the one on the right. Is it possible with Power Query or SQL? If so, how can I do it?



 

Need Help
  • Uzi2019's avatar
    Uzi2019
    2 years ago

    Hi Anonymous 

     

    Do you want to have a seperate table??? 

     

    Like this??


    then take new table from modeling tab then write this 

     



    Summarize table = SUMMARIZE(Table3,Table3[RN],
    "Min CNV",CALCULATE(MIN(Table3[CVN]),ALLEXCEPT(Table3, Table3[RN])),
    "Max CNV",CALCULATE(Max(Table3[CVN]),ALLEXCEPT(Table3, Table3[RN])))
     

     

     

     

    I hope I answered your question!

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    If the first and last are determined by the [Score] field, try creating a calculated table with the following code:

    Table2 = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    "first_CVN",
    VAR __min_score = CALCULATE( MIN('Table'[Score]), ALLEXCEPT('Table','Table'[ID]) )
    VAR __result = CALCULATE(MAX('Table'[Cvn]),'Table'[Score]=__min_score,ALLEXCEPT('Table','Table'[ID]))
    RETURN
    __result,
    "last_CVN",
    VAR __max_score = CALCULATE( MAX('Table'[Score]), ALLEXCEPT('Table','Table'[ID]) )
    VAR __result = CALCULATE(MAX('Table'[Cvn]),'Table'[Score]=__max_score,ALLEXCEPT('Table','Table'[ID]))
    RETURN
    __result
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

7 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    hi Anonymous 

     

    just simply take matrix visual and add two times cvn and set to first and last

     

     

    I hope I naswered your question!

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I was planning to create a relationship table with a matrix table, but I couldn't do it with matrix table

      • Uzi2019's avatar
        Uzi2019
        Community Champion
        2 years ago

        Hi Anonymous 

         

        I didnt understand you query..
        Relationship table with Matrix table???



  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    If the first and last are determined by the [Score] field, try creating a calculated table with the following code:

    Table2 = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    "first_CVN",
    VAR __min_score = CALCULATE( MIN('Table'[Score]), ALLEXCEPT('Table','Table'[ID]) )
    VAR __result = CALCULATE(MAX('Table'[Cvn]),'Table'[Score]=__min_score,ALLEXCEPT('Table','Table'[ID]))
    RETURN
    __result,
    "last_CVN",
    VAR __max_score = CALCULATE( MAX('Table'[Score]), ALLEXCEPT('Table','Table'[ID]) )
    VAR __result = CALCULATE(MAX('Table'[Cvn]),'Table'[Score]=__max_score,ALLEXCEPT('Table','Table'[ID]))
    RETURN
    __result
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group